Thursday, November 4, 2010

Finding The Difference Between Two Dates that are in Seperate Rows in T-SQL

This problem is often solved with a cursor, but is easily re-written using a CTE. In the below sample, we are using data from a university who's trying to figure out the time between when a course ended and when the next course began. So the data looks something like this:
learnerID StartDate CompletedDate
0000131801 2010-07-01 00:00:00.000 2010-09-23 00:00:00.000
0000131801 2010-10-01 00:00:00.000 2010-12-24 00:00:00.000
0000212970 2009-11-01 00:00:00.000 2010-01-24 00:00:00.000
0000212970 2010-02-01 00:00:00.000 2010-04-26 00:00:00.000
0000212970 2010-03-01 00:00:00.000 2010-05-24 00:00:00.000
0000212970 2010-06-01 00:00:00.000 2010-08-24 00:00:00.000
0000212970 2010-07-01 00:00:00.000 2010-09-23 00:00:00.000
0000212970 2010-10-01 00:00:00.000 2010-12-24 00:00:00.000

and the query that will give them the average days between a course ending and the next course starting looks like this:

with lc
as
(
select row_number() over (order by learner_id, start_date, learner_course_id) as rownum
, learner_course_id
, learner_id
, start_date
, coalesce(completed_date, end_date) as CompletedDate
from learner_courses
)
select datediff(d, lc1.completeddate, lc2.start_date) as daysbetweenstarts, *
from lc lc1
join lc lc2
on lc1.learner_id = lc2.learner_id
and lc2.rownum = (lc1.rownum + 1)
where datediff(d, lc1.completeddate, lc2.start_date) > -1

No comments:

Post a Comment