title: Query to get results for every hour of the day even data if not present
body: <p>I am trying to get the query for getting the count of users every hour of day in the table. If the data for that hour is not present, I want to record the hour with count of zero. Also users should be counted only for their first entry. Subsequent entries should be ignored.</p>
  
  <p>Table:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Desired output:</p>
  
  <pre><code>Date                      UserCount
  2014-10-08 00:00:00.000      1
  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
  </code></pre>
  
  <p>My attempt:</p>
  
  <pre><code>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)
  </code></pre>
  
  <p>This only gives me per hour for the record that is present. Not the data for the missing hours. I think there is a way by using a cross join to get 0 data even for the missing hours.</p>
  
  <p>Something like this, I came across, but not able to construct a proper query with it. </p>
  
  <pre><code>cross join (select 
                  ROW_NUMBER() over (order by (select NULL)) as seqnum
              from 
                  INFORMATION_SCHEMA.COLUMNS) hours 
  where hours.seqnum &gt;= 24 
  </code></pre>
  
  <p>Once again, I am not a SQL expert, but trying hard to construct this result set. </p>
  
  <p>One more attempt :</p>
  
  <pre><code>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 (less than)= 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
  </code></pre>