date difference 1

[xxing]

Hi

I have the following table

ID date
1 1 Jan 2008
2 2 Jan 2008
3 5 Jan 2008

I want to see the following:

ID date difference
1 1 Jan 2008 Null
2 2 Jan 2008 1
3 4 Jan 2008 2

i.e it finds the day difference from the row above.

Thank you for your help

Regards
Mark

Regards
Mark

 

[RyanEK]

Hi,

You could do the following...

select t1.*,
(select top 1 datediff(d, t2.date, t1.date) 
 from [Table] t2 where t2.id < t1.id 
 order by t2.id desc) as Diff
from [Table] t1

Ry

 

[xxing]

Thank you RyanEk for your valuable post. You have put me on another track.

I initially thought cursors was the only way to do this. A star for you!!!

Regards
Mark