date comparison between two different 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 comparison between two different rows

Author

Topic

ms
Starting Member

11 Posts

Posted - 2008-10-13 : 09:48:41

Sample data

id start date end date
101 10/04/08 11/04/08
101 14/04/08 15/04/08
101 10/05/08 12/05/08

I want to write a query where the diff between end date of record 1 is compared with the start date of record 2. similarly diff between end date of record 2 and start date of record 3. Would someone please help me with this query.

Many Many thanks

MS

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-13 : 09:53:11

[code]SELECT t.id ,
t.startdate,
t.enddate ,
DATEDIFF(dd,t.enddate,t1.startdate) as diff
FROM table t
OUTER APPLY (SELECT TOP 1 Startdate
FROM table
WHERE id=t.id
AND startdate > t.enddate
ORDER BY startdate)t1[/code]

ms
Starting Member

11 Posts

Posted - 2008-10-13 : 10:10:13

Thanks for your reply

is OUTER APPLY supported in sql server 2000?

I tried the sql you have suggested...it is coming up with a Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'OUTER'.

Hence the above question

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-13 : 10:16:19

quote:
Originally posted by ms

Thanks for your reply

is OUTER APPLY supported in sql server 2000?

I tried the sql you have suggested...it is coming up with a Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'OUTER'.

Hence the above question

Thanks

nope. its not. i gave you sql 2005 specific solution since you posted this in sql 2005 forum. in future make sure you post in correct forum.Try the below solution anyways

SELECT t1.id,
t1.startdate,
DATEDIFF(dd,t1.enddate,MIN(t2.startdate)) AS diff
FROM table t1
LEFT JOIN table t2
ON t1.id=t2.id
AND t2.startdate>t1.enddate
GROUP BY t1.id,t1.startdate,t1.enddate

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources