February 9, 2011 at 10:14 am
I need to move Excel data to SQL. In Excel, it is easier to calculate duration:
If MEMBID is the same, make first row as null, second row duration=[second row].[DATEFROM]-[first row].[DATETO].
For example,
MEMBID=06000031,
first row duration=null,
second row duration=[05/30/2007]-[05/09/2007] = 21
third row duration=[07/22/2009]-[06/04/2007]=779
fourth row duration=[04/09/2010]-[07/24/2009]=259
and so on...
How to code in SQL is really hard for me. Can someone help me?
MEMBIDDATEFROMDATETOduration
------------------------------------------
0600003105/04/200705/09/2007
0600003105/30/200706/04/200721
0600003107/22/200907/24/2009779
0600003104/09/201004/11/2010259
0600003105/28/201005/30/201047
0600014506/09/200706/21/2007
0600014506/27/200707/20/20076
0600014508/07/200709/06/200718
0600014509/29/200710/05/200723
0600014510/23/200710/30/200718
0600014510/30/200711/02/20070
0600014505/28/200805/30/2008208
0600014508/09/200908/18/2009436
February 9, 2011 at 10:29 am
try using this example to achieve you requirements
declare @datefrom datetime,
@dateto datetime
set @datefrom = '05/30/2007'
set @dateto = '06/04/2007'
select DATEDIFF(dd,@datefrom,@dateto)
Here is link describing the datediff function
http://msdn.microsoft.com/en-us/library/ms189794.aspx
also, please read the link in my signature on ways to post questions to get the best help.
February 9, 2011 at 11:43 am
This problem becomes pretty easy if you can add a "RowID" to each member of a group with the same MembID. Then you can LEFT JOIN the table to itself and get the "To" date from the prior row using the Row ID value. If there isn't one, it returns NULL for the difference.
I modified your data to look like this:
[font="Courier New"]
MembID RowID DateFrom DateTo
60000311 2007-05-042007-05-09
60000312 2007-05-302007-06-04
60000313 2009-07-222009-07-24
600003142010-04-092010-04-11
600003152010-05-282010-05-30
600014512007-06-092007-06-21
600014522007-06-272007-07-20
600014532007-08-072007-09-06
600014542007-09-292007-10-05
600014552007-10-232007-10-30
600014562007-10-302007-11-02
600014572008-05-282008-05-30
600014582009-08-092009-08-18[/font]
Then ran this query:
SELECT a.MembID, a.RowID, a.DateFrom, a.DateTo, b.DateTo AS PriorDateTo, DATEDIFF(D, b.DateTo, a.DateFrom) AS Duration
FROM DiffTest a
LEFT JOIN DiffTest b
ON a.MembID = b.MembID
AND a.RowID = b.RowID + 1
ORDER BY 1, 2
and got this result:
[font="Courier New"]MembIDRowIDDateFromDateToPriorDateToDuration
600003112007-05-042007-05-09NULL NULL
600003122007-05-302007-06-042007-05-0921
600003132009-07-222009-07-242007-06-04779
600003142010-04-092010-04-112009-07-24259
600003152010-05-282010-05-302010-04-1147
600014512007-06-092007-06-21NULL NULL
600014522007-06-272007-07-202007-06-216
600014532007-08-072007-09-062007-07-2018
600014542007-09-292007-10-052007-09-0623
600014552007-10-232007-10-302007-10-0518
600014562007-10-302007-11-022007-10-300
600014572008-05-282008-05-302007-11-02208
600014582009-08-092009-08-182008-05-30436[/font]
February 9, 2011 at 12:18 pm
adonetok (2/9/2011)
I need to move Excel data to SQL. In Excel, it is easier to calculate duration:...
How to code in SQL is really hard for me. Can someone help me?
One of the tricks to changing from Excel to SQL is to remember that for calculations, you're either going to be doing a bunch of rows at once for a final total (group by), or that you need to get everything on one row before you start calculating.
You'll notice above the LEFT JOIN that connects the data back to itself, so the row and the previous row end up on the same line. The ON clause handles how to associate which row connects up with what other rows.
So, I've put some comments in the code below. Don't be scared by the WITH statements. It's the same statement every time. It's like a miniature VIEW, or saved select statement. It simply makes things easier to read once you're used to them. If you're more interested in them read up on 'Common Table Expressions'.
Also, note how at the beginning I made your code usable to others in a consumable form. This will help you in the future get better tested code.
IF OBJECT_ID( 'tempdb..#tmp') IS NOT NULL
DROP TABLE #tmp
CREATE TABLE #tmp (MembID VARCHAR(15), DateFrom DATETIME, DateTo DATETIME)
INSERT INTO #tmp
SELECT
'06000031', '05/04/2007', '05/09/2007' UNION ALL SELECT
'06000031', '05/30/2007', '06/04/2007' UNION ALL SELECT
'06000031', '07/22/2009', '07/24/2009' UNION ALL SELECT
'06000031', '04/09/2010', '04/11/2010' UNION ALL SELECT
'06000031', '05/28/2010', '05/30/2010' UNION ALL SELECT
'06000145', '06/09/2007', '06/21/2007' UNION ALL SELECT
'06000145', '06/27/2007', '07/20/2007' UNION ALL SELECT
'06000145', '08/07/2007', '09/06/2007' UNION ALL SELECT
'06000145', '09/29/2007', '10/05/2007' UNION ALL SELECT
'06000145', '10/23/2007', '10/30/2007' UNION ALL SELECT
'06000145', '10/30/2007', '11/02/2007' UNION ALL SELECT
'06000145', '05/28/2008', '05/30/2008' UNION ALL SELECT
'06000145', '08/09/2009', '08/18/2009'
select * from #tmp
-- Breaking out the code from this point:
; WITH cte AS
(SELECT
ROW_NUMBER() OVER ( PARTITION BY MembID ORDER BY DateFrom) AS RowNum, --Order By tells it how to give it numbers, partition when to restart the numbering.
*
FROM
#tmp
)
-- What that looks like:
SELECT * FROM cte
; WITH cte AS
(SELECT
ROW_NUMBER() OVER ( PARTITION BY MembID ORDER BY DateFrom) AS RowNum, --Order By tells it how to give it numbers, partition when to restart the numbering.
*
FROM
#tmp
)
-- Get the data on the same line:
SELECT
CurrRow.MembID, CurrRow.DateFrom, PrevRow.DateTo
FROM
cte AS CurrRow
LEFT JOIN
cte AS PrevRow --Notice, we're using the same table twice, but aliasing it.
ON CurrRow.RowNum = PrevRow.RowNum + 1 -- Bring in the row previous by aligning the numbers we assigned in the cte.
AND CurrRow.MembID = PrevRow.MembID -- Make sure that we only align associated rows.
-- So, now, your calculations:
; WITH cte AS
(SELECT
ROW_NUMBER() OVER ( PARTITION BY MembID ORDER BY DateFrom) AS RowNum, --Order By tells it how to give it numbers, partition when to restart the numbering.
*
FROM
#tmp
)
SELECT
CurrRow.MembID, CurrRow.DateFrom, CurrRow.DateTo, DATEDIFF( dd, PrevRow.DateTo, CurrRow.DateFrom) AS Duration
FROM
cte AS CurrRow
LEFT JOIN
cte AS PrevRow --Notice, we're using the same table twice, but aliasing it.
ON CurrRow.RowNum = PrevRow.RowNum + 1 -- Bring in the row previous by aligning the numbers we assigned in the cte.
AND CurrRow.MembID = PrevRow.MembID -- Make sure that we only align associated rows.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 9, 2011 at 12:30 pm
Thank all of you.
I try to run code from SSCommitted but I got an error as below. How to fix it?
Our company is still using SQL 2000. I do not think ROW_NUMBER is supported.
Msg 156, Level 15, State 1, Line 26
Incorrect syntax near the keyword 'WITH'.
Msg 195, Level 15, State 10, Line 28
'ROW_NUMBER' is not a recognized function name.
Msg 156, Level 15, State 1, Line 37
Incorrect syntax near the keyword 'WITH'.
Msg 195, Level 15, State 10, Line 39
'ROW_NUMBER' is not a recognized function name.
Msg 156, Level 15, State 1, Line 57
Incorrect syntax near the keyword 'WITH'.
Msg 195, Level 15, State 10, Line 59
'ROW_NUMBER' is not a recognized function name.
February 9, 2011 at 12:50 pm
adonetok (2/9/2011)
Thank all of you.I try to run code from SSCommitted but I got an error as below. How to fix it?
Our company is still using SQL 2000. I do not think ROW_NUMBER is supported.
ah, you need to be more careful about which forum you post in. There's an entire forum tree for SQL 7/2000. That makes life much more complex. It'll require what's called triangular joins to find the correct MAX row just previous to the current row.
Hopefully someone will have some time to help you construct that, or I'll try to swing in later when I have more time. Haven't done one in a bit and have to think it through.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 9, 2011 at 2:02 pm
In the real code, remove the 'WhatItFinds' column, I merely left it in there as an example.. You don't want to call a correlated subquery twice. This is probably the simplest way of arriving at the result. It's not the quickest but it'll get you started.
IF OBJECT_ID( 'tempdb..#tmp') IS NOT NULL
DROP TABLE #tmp
CREATE TABLE #tmp (MembID VARCHAR(15), DateFrom DATETIME, DateTo DATETIME)
INSERT INTO #tmp
SELECT
'06000031', '05/04/2007', '05/09/2007' UNION ALL SELECT
'06000031', '05/30/2007', '06/04/2007' UNION ALL SELECT
'06000031', '07/22/2009', '07/24/2009' UNION ALL SELECT
'06000031', '04/09/2010', '04/11/2010' UNION ALL SELECT
'06000031', '05/28/2010', '05/30/2010' UNION ALL SELECT
'06000145', '06/09/2007', '06/21/2007' UNION ALL SELECT
'06000145', '06/27/2007', '07/20/2007' UNION ALL SELECT
'06000145', '08/07/2007', '09/06/2007' UNION ALL SELECT
'06000145', '09/29/2007', '10/05/2007' UNION ALL SELECT
'06000145', '10/23/2007', '10/30/2007' UNION ALL SELECT
'06000145', '10/30/2007', '11/02/2007' UNION ALL SELECT
'06000145', '05/28/2008', '05/30/2008' UNION ALL SELECT
'06000145', '08/09/2009', '08/18/2009'
select * from #tmp
select
t.MembID,
t.DateFrom,
t.DateTo,
(SELECT MAX( DateTo) FROM #tmp AS t2 WHERE t2.DateTo <= t.DateFrom AND t2.MembID = t.membID) AS WhatItFinds,
DATEDIFF( dd,
(SELECT MAX( DateTo) FROM #tmp AS t2 WHERE t2.DateTo <= t.DateFrom AND t2.MembID = t.membID),
DAteFrom) AS Duration
FROM
#tmp AS t
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 10, 2011 at 12:53 pm
SSCommitted, thank you so much. It works great!!!
February 10, 2011 at 1:05 pm
adonetok (2/10/2011)
SSCommitted, thank you so much. It works great!!!
Heheh, my pleasure, glad it works for you. You understand what it's doing, I hope?
Also, just an FYI, my name's Craig Farrell, the SSCommitted thing is just a post count title, like most other forums (for example, right now you're "Valued Member"). However, you did make me chuckle, so thank you. 😀
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply