6

I am trying to write a query to show the count of users for each hour of the day. If there is no data for that hour, I want to record the hour with a count of zero.

Source data

╔════════╦═════════════════════════╗
║ userId ║      creationDate       ║
╠════════╬═════════════════════════╣
║      1 ║ 2014-10-08 14:33:20.763 ║
║      2 ║ 2014-10-09 04:24:14.283 ║
║      3 ║ 2014-10-10 18:34:26.260 ║
╚════════╩═════════════════════════╝ 

Desired output

╔═════════════════════════╦═══════════╗
║          Date           ║ UserCount ║
╠═════════════════════════╬═══════════╣
║ 2014-10-08 01:00:00.000 ║         1 ║
║ 2014-10-08 02:00:00.000 ║         1 ║
║ 2014-10-08 03:00:00.000 ║         0 ║
║ 2014-10-08 04:00:00.000 ║         1 ║
║ ....                    ║           ║
║ .....                   ║           ║
║ 2014-10-10 23:00:00.000 ║         1 ║
║ 2014-10-10 00:00:00.000 ║         0 ║
╚═════════════════════════╩═══════════╝

Attempt 1

SELECT
    CAST(creationDate as date) AS ForDate, 
    DATEPART(hour,date) AS OnHour, 
    COUNT(distinct userId) AS Totals
FROM Table
where primaryKey= 123
GROUP BY
    CAST(creationDate as date), 
    DATEPART(hour, createDate);

This only gives me counts per hour for records that are present, nothing for the missing hours. I think there is a way by using a cross join to get a zero count for the missing hours.

Something like this, I came across, but not able to construct a proper query with it:

cross join (select ROW_NUMBER() over (order by (select NULL)) as seqnum
from INFORMATION_SCHEMA.COLUMNS) hours where hours.seqnum <= 24 

I am not a SQL expert, but I am trying hard to construct this result set.

Attempt 2:

with dh as (
     select DATEADD(hour, seqnum - 1, thedatehour ) as DateHour 
     from (select distinct cast(cast(createDate as DATE) as datetime) as thedatehour
           from Table a 
          ) a 
          cross join
          (select ROW_NUMBER() over (order by (select NULL)) as seqnum
           from INFORMATION_SCHEMA.COLUMNS
          ) hours
          where hours.seqnum <= 24
    )
select dh.DateHour, COUNT(distinct c.userId) 
from dh cross join Table c
--on dh.DateHour = c.createDate
group by dh.DateHour
order by 1
0

1 Answer 1

5

Take the query you already have and capture the output in a temp table #T.

select cast(T.creationDate as date) as ForDate,
       datepart(hour, T.creationDate) as OnHour,
       count(distinct T.userId) as Totals
into #T
from dbo.T
group by cast(T.creationDate as date),
         datepart(hour, T.creationDate)

Get the distinct ForDate values from #T and cross apply against a list of hours for one day and left outer join #T to get the Totals for each hour.

select dateadd(hour, H.OnHour, cast(D.ForDate as datetime)) as ForDate,
       isnull(T.Totals, 0) as Totals
from (select distinct T.ForDate from #T as T) as D
cross apply (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),
                   (13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23)) as H(OnHour)
  left outer join #T as T
    on H.OnHour = T.OnHour and
       D.ForDate = T.ForDate

drop table #T
2
  • What would be the MySQL query for the above ?
    – Biranchi
    Commented Sep 12, 2016 at 21:47
  • @Biranchi Sorry, I don't know MySQL. Ask a new question on the site. Commented Sep 13, 2016 at 5:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.