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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

stamford
Starting Member

United Kingdom
47 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

4614 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

4614 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
47 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

4614 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
47 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
52326 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
47 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  
 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.19 seconds. Powered By: Snitz Forums 2000