Results for every hour of the day even data if not present

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 s

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

StackExchange Database Administrators Q#86486, answer score: 5