SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Date difference between two rows
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

stamford
Starting Member

United Kingdom
37 Posts

Posted - 01/07/2014 :  17:00:32  Show Profile  Reply with Quote

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
Flowing Fount of Yak Knowledge

4346 Posts

Posted - 01/07/2014 :  18:31:07  Show Profile  Reply with Quote
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
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4346 Posts

Posted - 01/07/2014 :  18:38:34  Show Profile  Reply with Quote
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
Go to Top of Page

stamford
Starting Member

United Kingdom
37 Posts

Posted - 01/07/2014 :  18:55:00  Show Profile  Reply with Quote
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 ?
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4346 Posts

Posted - 01/07/2014 :  19:23:26  Show Profile  Reply with Quote
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.
Go to Top of Page

stamford
Starting Member

United Kingdom
37 Posts

Posted - 01/07/2014 :  19:53:28  Show Profile  Reply with Quote
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?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 01/08/2014 :  06:02:59  Show Profile  Reply with Quote
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
Go to Top of Page

stamford
Starting Member

United Kingdom
37 Posts

Posted - 01/08/2014 :  08:08:49  Show Profile  Reply with Quote
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?
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000