Geek asks and answers

Hourly based time data aggregation

I have a table in MSSQL from which two columns look like an example below. I want to find out the number of rows that I have for all hours of the session.

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

As a result, the result will look something like this:

Session Time          Number of Records
2011-06-09 23:00:00   4
2011-06-09 22:00:00   4
+3
source share
2 answers
-- 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)
+5
source

this can help

declare @tbl table    
(    
RangeCount int,     
TimeRange INT
)    


insert into @tbl (TimeRange) values (0)    
insert into @tbl (TimeRange) values (1)    
insert into @tbl (TimeRange) values (2)    
insert into @tbl (TimeRange) values (3)    
insert into @tbl (TimeRange) values (4)    
insert into @tbl (TimeRange) values (5)    
insert into @tbl (TimeRange) values (6)    
insert into @tbl (TimeRange) values (7)    
insert into @tbl (TimeRange) values (8)    
insert into @tbl (TimeRange) values (9)    
insert into @tbl (TimeRange) values (10)    
insert into @tbl (TimeRange) values (11)    
insert into @tbl (TimeRange) values (12)    
insert into @tbl (TimeRange) values (13)    
insert into @tbl (TimeRange) values (14)    
insert into @tbl (TimeRange) values (15)    
insert into @tbl (TimeRange) values (16)    
insert into @tbl (TimeRange) values (17)    
insert into @tbl (TimeRange) values (18)    
insert into @tbl (TimeRange) values (19)    
insert into @tbl (TimeRange) values (20)    
insert into @tbl (TimeRange) values (21)    
insert into @tbl (TimeRange) values (22)    
insert into @tbl (TimeRange) values (23)    



insert into @tbl (    
RangeCount,     
TimeRange)     

select count(1), datediff(hh,GETDATE(),SessionTime)
from tablename (NOLOCK)    

group by datediff(hh,GETDATE(),SessionTime)

select SUM(ISNULL(RangeCount, 0))as TotalCount,     

CASE TimeRange    
WHEN 0 THEN  '00:00 - 01:00'    
WHEN 1 THEN  '01:00 - 02:00'    
WHEN 2 THEN  '02:00 - 03:00'    
WHEN 3 THEN  '03:00 - 04:00'    
WHEN 4 THEN  '04:00 - 05:00'    
WHEN 5 THEN  '05:00 - 06:00'    
WHEN 6 THEN  '06:00 - 07:00'    
WHEN 7 THEN  '07:00 - 08:00'    
WHEN 8 THEN  '08:00 - 09:00'    
WHEN 9 THEN  '09:00 - 10:00'    
WHEN 10 THEN  '10:00 - 11:00'    
WHEN 11 THEN  '11:00 - 12:00'    
WHEN 12 THEN  '12:00 - 13:00'    
WHEN 13 THEN  '13:00 - 14:00'    
WHEN 14 THEN  '14:00 - 15:00'    
WHEN 15 THEN  '15:00 - 16:00'    
WHEN 16 THEN  '16:00 - 17:00'    
WHEN 17 THEN  '17:00 - 18:00'    
WHEN 18 THEN  '18:00 - 19:00'    
WHEN 19 THEN  '19:00 - 20:00'    
WHEN 20 THEN  '20:00 - 21:00'    
WHEN 21 THEN  '21:00 - 22:00'    
WHEN 22 THEN  '22:00 - 23:00'    
WHEN 23 THEN  '23:00 - 24:00'    
END AS TimeRange    
from @tbl    
group by TimeRange
-1
source

More articles:


All Articles