Date difference between two rows

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Date difference between two rows

Author

Topic

stamford
Starting Member

47 Posts

Posted - 2014-01-07 : 17:00:32

What script will give me the values for the column STAGE_DAYS in the table below, which is the difference between the EVENT_DATE value of the current row and the previous one, or the difference between the equivalent values of PATHWAY_DAYS - either would do the job.
I can already generate the values for the cumulative total of PATHWAY_DAYS.
The table has no primary key and the script needs to be backwards compatible with SQL 2000. Many thanks.


EVENT_DATE	STAGE_DAYS	PATHWAY_DAYS
01/08/2012	0	        0
02/08/2012	1	        1
12/08/2012	10	        11
09/12/2012	119	        130
31/12/2012	22	        152
31/12/2012	0	        152
04/01/2013	4	        156
04/01/2013	0	        156
05/01/2013	1	        157
16/01/2013	11	        168
27/01/2013	11	        179
06/02/2013	10	        189
06/02/2013	0	        189
26/02/2013	20	        209
01/04/2013	34	        243

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-01-07 : 18:31:07

[code]DECLARE @Foo TABLE (EVENT_DATE DATETIME)

INSERT @Foo VALUES
(CONVERT(DATETIME, '01/08/2012', 103)),
(CONVERT(DATETIME, '02/08/2012', 103)),
(CONVERT(DATETIME, '12/08/2012', 103)),
(CONVERT(DATETIME, '09/12/2012', 103)),
(CONVERT(DATETIME, '31/12/2012', 103)),
(CONVERT(DATETIME, '31/12/2012', 103)),
(CONVERT(DATETIME, '04/01/2013', 103)),
(CONVERT(DATETIME, '04/01/2013', 103)),
(CONVERT(DATETIME, '05/01/2013', 103)),
(CONVERT(DATETIME, '16/01/2013', 103)),
(CONVERT(DATETIME, '27/01/2013', 103)),
(CONVERT(DATETIME, '06/02/2013', 103)),
(CONVERT(DATETIME, '06/02/2013', 103)),
(CONVERT(DATETIME, '26/02/2013', 103)),
(CONVERT(DATETIME, '01/04/2013', 103))

SELECT
A.EVENT_DATE,
COALESCE(DATEDIFF(DAY, B.EVENT_DATE, A.EVENT_DATE), 0) AS STAGE_DAYS
FROM
(
SELECT
*
,ROW_NUMBER() OVER (ORDER BY EVENT_DATE) AS RowNum
FROM @Foo
) AS A
LEFT OUTER JOIN
(
SELECT
*
,ROW_NUMBER() OVER (ORDER BY EVENT_DATE) AS RowNum
FROM @Foo
) AS B
ON A.RowNum = B.RowNum + 1[/code]

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-01-07 : 18:38:34

Missed that you wanted this to work with SQL 2000(is that still used?) ;)

SELECT
	A.EVENT_DATE,
	COALESCE(DATEDIFF(DAY, A.EVENT_DATE, B.EVENT_DATE), 0) AS STAGE_DAYS
FROM
	(
		SELECT 
			*
			,(SELECT COUNT(*) FROM @Foo AS f2 WHERE f.EVENT_DATE <= f2.EVENT_DATE) AS RowNum
		FROM @Foo AS f
	) AS A
LEFT OUTER JOIN
	(
		SELECT 
			*
			,(SELECT COUNT(*) FROM @Foo AS f2 WHERE f.EVENT_DATE <= f2.EVENT_DATE) AS RowNum
		FROM @Foo AS f
	) AS B
	ON A.RowNum = B.RowNum + 1
ORDER BY 
	A.EVENT_DATE

stamford
Starting Member

47 Posts

Posted - 2014-01-07 : 18:55:00

quote:

Originally posted by Lamprey

Missed that you wanted this to work with SQL 2000(is that still used?) ;)

SELECT
	A.EVENT_DATE,
	COALESCE(DATEDIFF(DAY, A.EVENT_DATE, B.EVENT_DATE), 0) AS STAGE_DAYS
FROM
	(
		SELECT 
			*
			,(SELECT COUNT(*) FROM @Foo AS f2 WHERE f.EVENT_DATE <= f2.EVENT_DATE) AS RowNum
		FROM @Foo AS f
	) AS A
LEFT OUTER JOIN
	(
		SELECT 
			*
			,(SELECT COUNT(*) FROM @Foo AS f2 WHERE f.EVENT_DATE <= f2.EVENT_DATE) AS RowNum
		FROM @Foo AS f
	) AS B
	ON A.RowNum = B.RowNum + 1
ORDER BY 
	A.EVENT_DATE

Hi. thanks for this but the value of every cell in the column evaluates to 1 ?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-01-07 : 19:23:26

No, the values are not 1. However, I see that you have duplicate dates, so my solution won't work for you. I think you'll need to dump the values into a temp table with a Identity column and then use that column to order the values.

stamford
Starting Member

47 Posts

Posted - 2014-01-07 : 19:53:28

quote:
Originally posted by Lamprey

No, the values are not 1. However, I see that you have duplicate dates, so my solution won't work for you. I think you'll need to dump the values into a temp table with a Identity column and then use that column to order the values.

I am already using a temporary table and a SELECT INTO statement so I could add an ALTER statement and add an IDENTITY field and then use an UPDATE statement to populate the new field. If there were a new field called ID_FIELD how would this then affect your script?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-08 : 06:02:59

something like this


SELECT TOP 0 * INTO #Temp
FROM YourMainTable

ALTER TABLE #Temp ADD ID int IDENTITY(1,1)

INSERT #Temp (
EVENT_DATE
)
SELECT EVENT_DATE
FROM YourmainTable
ORDER BY EVENT_DATE


SELECT t1.EVENT_DATE,
COALESCE(DATEDIFF(dd,t2.EVENT_DATE,t1.EVENT_DATE),0) AS STAGE_DAYS
INTO #Final
FROM #Temp t1
LEFT JOIn #temp t2
ON t2.ID = t1.ID - 1

SELECT EVENT_DATE,STAGE_DAYS,
(SELECT SUM(STAGE_DAYS) FROM #Final WHERE ID < = f.ID) AS PATHWAY_DAYS
FROM #Final f

DROP TABLE #Final
DROP TABLE #Temp

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

stamford
Starting Member

47 Posts

Posted - 2014-01-08 : 08:08:49

quote:
Originally posted by visakh16

something like this

SELECT TOP 0 * INTO #Temp
FROM YourMainTable

ALTER TABLE #Temp ADD ID int IDENTITY(1,1)

INSERT #Temp (
EVENT_DATE
)
SELECT EVENT_DATE
FROM YourmainTable
ORDER BY EVENT_DATE

SELECT t1.EVENT_DATE,
COALESCE(DATEDIFF(dd,t2.EVENT_DATE,t1.EVENT_DATE),0) AS STAGE_DAYS
INTO #Final
FROM #Temp t1
LEFT JOIn #temp t2
ON t2.ID = t1.ID - 1

SELECT EVENT_DATE,STAGE_DAYS,
(SELECT SUM(STAGE_DAYS) FROM #Final WHERE ID < = f.ID) AS PATHWAY_DAYS
FROM #Final f

DROP TABLE #Final
DROP TABLE #Temp

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

thank you, i'll give that a try. is this script SQL 2000 compatible?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources