tsql PIVOT function

Need help with the following query:

Current Data format:

StudentID      EnrolledStartTime          EnrolledEndTime

1              7/18/2011 1.00 AM          7/18/2011 1.05 AM
2              7/18/2011 1.00 AM          7/18/2011 1.09 AM
3              7/18/2011 1.20 AM          7/18/2011 1.40 AM
4              7/18/2011 1.50 AM          7/18/2011 1.59 AM
5              7/19/2011 1.00 AM          7/19/2011 1.05 AM
6              7/19/2011 1.00 AM          7/19/2011 1.09 AM
7              7/19/2011 1.20 AM          7/19/2011 1.40 AM
8              7/19/2011 1.10 AM          7/18/2011 1.59 AM

I would like to calculate the time difference between EnrolledEndTime and EnrolledStartTime and group it with 15 minutes difference and the count of students that enrolled in the time.

Expected Result :

Count(StudentID)   Date    0-15Mins  16-30Mins 31-45Mins 46-60Mins

4               7/18/2011   3           1         0             0

4               7/19/2011   2           1         0             1

Can I use a开发者_JS百科 combination of the PIVOT function to acheive the required result. Any pointers would be helpful.

Create a table variable/temp table that includes all the columns from the original table, plus one column that marks the row as 0, 16, 31 or 46. Then

SELECT * FROM temp table name PIVOT (Count(StudentID) FOR new column name in (0, 16, 31, 46).

That should put you pretty close.

It's possible (just see the basic pivot instructions here: http://msdn.microsoft.com/en-us/library/ms177410.aspx), but one problem you'll have using pivot is that you need to know ahead of time which columns you want to pivot into.

E.g., you mention 0-15, 16-30, etc. but actually, you have no idea how long some students might take -- some might take 24-hours, or your full session timeout, or what have you.

So to alleviate this problem, I'd suggesting having a final column as a catch-all, labeled something like '>60'.

Other than that, just do a select on this table, selecting the student ID, the date, and a CASE statement, and you'll have everything you need to work the pivot on.

CASE WHEN date2 - date1 < 15 THEN '0-15' WHEN date2-date1 < 30 THEN '16-30'...ELSE '>60' END.

I have an old version of ms sql server that doesn't support pivot. I wrote the sql for getting the data. I cant test the pivot, so I tried my best, couldn't test the pivot part. The rest of the sql will give you the exact data for the pivot table. If you accept null instead of 0, it can be written alot more simple, you can skip the "a subselect" part defined in "with a...".

declare @t table (EnrolledStartTime datetime,EnrolledEndTime datetime)
insert @t values('2011/7/18 01:00',  '2011/7/18 01:05')
insert @t values('2011/7/18 01:00',  '2011/7/18 01:09')
insert @t values('2011/7/18 01:20',  '2011/7/18 01:40')
insert @t values('2011/7/18 01:50',  '2011/7/18 01:59')
insert @t values('2011/7/19 01:00',  '2011/7/19 01:05')
insert @t values('2011/7/19 01:00',  '2011/7/19 01:09')
insert @t values('2011/7/19 01:20',  '2011/7/19 01:40')
insert @t values('2011/7/19 01:10',  '2011/7/19 01:59')

;with a 
as
(select * from
(select distinct dateadd(day, cast(EnrolledStartTime as int), 0) date from @t) dates
cross join 
(select '0-15Mins' t,  0 group1 union select '16-30Mins',  1 union select '31-45Mins',  2 union select '46-60Mins',  3) i) 
, b as 
(select (datediff(minute, EnrolledStartTime, EnrolledEndTime )-1)/15 group1, dateadd(day, cast(EnrolledStartTime as int), 0) date
from @t)
select count(b.date) count, a.date, a.t, a.group1 from a
left join b 
on a.group1 = b.group1
and a.date = b.date 
group by a.date, a.t, a.group1
-- PIVOT(max(date)  
-- FOR group1
-- in(['0-15Mins'], ['16-30Mins'], ['31-45Mins'], ['46-60Mins'])AS p