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?

(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