MrRAW MrRAW -4 years ago 55
SQL Question

Need to find the datediff between more then two dates

i need some ides

I have this table i need to search the datediff thats higer then xx days between more then two dates.
The table looks like this

ID DATE
1 2001-09-27
1 2002-02-11
1 2002-11-29
1 2003-11-24
1 2004-03-08
2 2003-11-24
2 2003-11-24
2 2004-11-24
3 2006-11-24
3 2007-11-24


I need it to show:

ID DATE DIF
1 2001-09-27 137 (DATEDIFF(day,'2001-09-27','2002-02-11'))
1 2002-02-11 291 (DATEDIFF(day,'2002-02-11','2002-11-29'))
1 2002-11-29 360 (DATEDIFF(day,'2002-11-29','2003-11-24'))
1 2003-11-24 AND SO ON..
1 2004-03-08
2 2003-11-24
2 2003-11-24
2 2004-11-24
3 2006-11-24
3 2007-11-24


So if the ID is the same I want to check the first date to the second date, then the second date to the third and so on.

You guys got som ides?

Answer Source

(Edited for Duplicate Dates)

Example

Declare @YourTable Table ([ID] int,[DATE] date)
Insert Into @YourTable Values
 (1,'2001-09-27')
,(1,'2002-02-11')
,(1,'2002-11-29')
,(1,'2003-11-24')
,(1,'2004-03-08')
,(2,'2003-11-24')
,(2,'2003-11-24')
,(2,'2004-11-24')
,(3,'2006-11-24')
,(3,'2007-11-24')

Select A.*
      ,[Diff]   = IsNull(DateDiff(DAY,A.[Date],B.[Date]),0) 
 From  @YourTable A
 Outer Apply (Select Date=min(Date) From @YourTable Where ID=A.ID and Date>A.Date) B
 Order By A.ID,A.Date

Returns

ID  DATE        Diff
1   2001-09-27  137
1   2002-02-11  291
1   2002-11-29  360
1   2003-11-24  105
1   2004-03-08  0
2   2003-11-24  366   -- Notice Dupe Date
2   2003-11-24  366   -- Notice Dupe Date
2   2004-11-24  0
3   2006-11-24  365
3   2007-11-24  0
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download