CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Eray Balkanli
Eray Balkanli

Reputation: 7990

How to count records in a table based on each (per) hour in SQL Server?

In SQL Server 2008, I have data like this (Case: varchar(20), Time: time):

Case   Time
-------------
D1     18:44
D2     19:12  
C1     21:20
F2     21:05
...

What I would like to do is to count cases per hour. Should include all cases.

Expected result:

.... Column18  Column19  Column20  Column21 ...
        1         1         0          2

where Column18 refers to the cases between 18:00 and 18:59, and same logic for others. I have from Column0 to Column23, 1 column per hour...

What I am doing is:

Select 
    ...
    ,  Column18 = sum(CASE WHEN Time like '18:%' THEN 1 ELSE 0 END)
    ,  Column19 = sum(CASE WHEN Time like '19:%' THEN 1 ELSE 0 END)
    ,  Column20 = sum(CASE WHEN Time like '20:%' THEN 1 ELSE 0 END)
    ,  Column21 = sum(CASE WHEN Time like '21:%' THEN 1 ELSE 0 END)
    ...
from 
    mytable

Even though my query works, it is long and repetitive, so it does not seem professional to me. I wonder if there is any better way to handle this situation. Any advice would be appreciated.

Upvotes: 1

Views: 848

Answers (3)

Eray Balkanli
Eray Balkanli

Reputation: 7990

Here is the simplest answer I could come up. Thanks a lot for all the advices. Looks way better now:

create table #temp (CaseID varchar(20),TheTime time)

insert into #temp values ('A1','03:56')
insert into #temp values ('A2','03:12')
insert into #temp values ('B2','03:21')
insert into #temp values ('C1','05:12')
insert into #temp values ('B3','06:00')
insert into #temp values ('B4','07:14')
insert into #temp values ('B5','07:18')
insert into #temp values ('D1','18:44')
insert into #temp values ('D2','19:54')
insert into #temp values ('C2','21:12')
insert into #temp values ('F4','21:50')

select *
from (
       select CaseID, DATEPART(hour,TheTime) as HourOfDay
       from #temp
     ) t
PIVOT
(
  Count(CaseID)
  FOR HourOfDay IN ([00],[01],[02],[03],[04],[05],[06],[07],[08],
                    [09],[10],[11],[12],[13],[14],[15],[16],[17],
                    [18],[19],[20],[21],[22],[23])
) AS PivotTable

Upvotes: 0

DatabaseCoder
DatabaseCoder

Reputation: 2032

We can go with Dynamic Pivot -

declare @ColString varchar(1000)=''
;with cte as(
    select 0 as X
    union all
    select x+1 as X
    from cte where X <23
)
select @ColString = @ColString + ',[Column' + cast(X as varchar) + ']' from cte
select @ColString = stuff(@ColString,1,1,'')


declare @DynamicQuery varchar(3000)=''
select @DynamicQuery = 
'select *
from (
    select [case],''Column''+cast(datepart(hh,[time]) as varchar) as [time]
    from #xyz
) src
pivot
(
    count([case]) for [Time] in ('+ @ColString + ')
) piv'


exec (@DynamicQuery)

Input data -

create table #xyz ([Case] varchar(10),[Time] time(0))

insert into #xyz
select 'D1','18:44' union all
select 'D2','19:12' union all
select 'C1','21:20' union all
select 'F2','21:05'

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270793

Your query is basically fine, but I strongly discourage you from using string functions on date/time columns.

datepart() is definitely one solution:

Select ...,
       Column18 = sum(CASE WHEN datepart(hour, Time) = 18 THEN 1 ELSE 0 END)
       Column19 = sum(CASE WHEN datepart(hour, Time) = 19 THEN 1 ELSE 0 END)

Direct comparison is more verbose, but more flexible:

select . . .,
       sum(case when time >= '18:00' and time < '19:00' then 1 else 0 end) as column18,
       sum(case when time >= '19:00' and time < '20:00' then 1 else 0 end) as column19,

Note that this uses as. SQL Server supports the syntax alias =. However, other databases do not use such syntax, so I prefer to stick with the ANSI-standard method of defining aliases.

Putting the values on rows instead of columns is probably the more "typical" solution:

select datepart(time, hour) as hr, count(*)
from t
group by datepart(time, hour)
order by hr;

As written, this will not return hours with zero counts.

Upvotes: 1

Related Questions