CODEWITHSUNDEEP
asp.netsql-serverdatabase
Akshay Jain
Akshay Jain

Reputation: 790

Display hourly based data for 24 hour in SQL Server

I want to display the hourly based report for the last 24 hour. I have tried but the problem is that it will display count only where particular hour contains data.

But I want to display count for an hour and if count not found then display 0 over there.

select 
    datepart(hour, upload_date) as [hour], count(*)
from 
    tbl_stories
where 
    upload_date > getdate() - 1
group by 
    datepart(hour, upload_date)

Output:

hour    count
-------------
11      2
16      1
17      1

but I want to get a record in the following way.

hour   count
-------------
1       0
2       0
3       5
.
.
.
.
24       1

Upvotes: 3

Views: 1389

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You can use a value() clause to generate all the hours and then use left join:

select v.hh, count(s.upload_date)
from (values (0), (1), . . . (23)
     ) v(hh) left join
     tbl_stories s
     on datepart(hour, s.upload_date) = v.hh and
        s.upload_date > getdate() - 1
group by v.hh
order by v.hh;

Note that hours go from 0 to 23.

If you don't want to list out the hours, a convenient generation method is a recursive CTE:

with hours as (
      select 1 as hh
      union all
      select hh + 1
      from hours
      where hh < 23
     )
select h.hh, count(s.upload_date)
from hours h
     tbl_stories s
     on datepart(hour, s.upload_date) = h.hh and
        s.upload_date > getdate() - 1
group by h.hh
order by h.hh;

Upvotes: 6

Related Questions