Aggregating Time Data Based on the Hour

Question

I have a table in MSSQL, of which the two columns look like the example below. I want to figure out the number of rows I have for all the hours in the Session Time.

Session Time          Duration
2011-06-09 23:47:07   00:01:51
2011-06-09 23:54:45   00:12:37
2011-06-09 23:41:17   00:21:57
2011-06-09 23:41:54   00:34:35
2011-06-09 22:47:51   00:01:20
2011-06-09 22:56:04   00:09:33
2011-06-09 22:55:40   00:11:08
2011-06-09 22:47:30   00:05:06

In the end, the result would look something like this:

Session Time          Number of Records
2011-06-09 23:00:00   4
2011-06-09 22:00:00   4

Answer 1

-- Test data
;with YourTable([Session Time]) as
(
select cast('2011-06-09 23:47:07' as datetime) union all
select '2011-06-09 23:54:45' union all
select '2011-06-09 23:41:17' union all
select '2011-06-09 23:41:54' union all
select '2011-06-09 22:47:51' union all
select '2011-06-09 22:56:04' union all
select '2011-06-09 22:55:40' union all
select '2011-06-09 22:47:30'
)

-- Query
select dateadd(hh, datediff(hh, 0, [Session Time]), 0) as [Session Time],
       count(*) as [Number of Records]
from YourTable
group by dateadd(hh, datediff(hh, 0, [Session Time]), 0)