Current date, previous date and the delta
-
I need help! I have a table with a date column. I think there is a set based solution for this but I can't seem to make any traction.
DateTime, other fields...
1/31/2006
2/17/2006
2/20/2006
2/25/2006 and so on...
Now I need the query to show the Date for the current record, Date for the previous record and the duration between.
Like:
"Current", "Previous", "Delta"
2/17/2006, 1/31/2006, 17
2/20/2006, 2/17/2006, 3
2/25/2006, 2/20/2006, 5
Thanks,
Dave
-
This is really easiest done if you have sequential numbering of the rows (i.e. via an IDENTITY column). So if you had:
row date
1 2/17/2006
2 2/21/2006
3 2/22/2006
You'd then do something like this using a self-join:
SELECT DATEDIFF(dd, t1.date, t2.date) as delta
FROM table t1
JOIN table t2
ON t1.row = t2.row + 1
-
Select T1.Date as [Current], MAX(T2.Date) as [Previous], DATEDIFF(dd, MAX(T2.Date), T1.Date ) as [Delta]
FROM Table T1
LEFT JOIN Table T2 on T2.Date < T1.Date
_____________
Code for TallyGenerator -
You may need a group by clause
Select T1.Date as [Current], MAX(T2.Date) as [Previous], DATEDIFF(dd, MAX(T2.Date), T1.Date ) as [Delta]
FROM Table T1
LEFT JOIN Table T2 on T2.Date < T1.Date
group by t1.date
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply