title: Select Consecutive Numbers in SQL
body: <p>This feels simple, but I can't find an answer anywhere.<br />
  I'm trying to run a query by time of day for each hour.  So I'm doing a <code>Group By</code> on the hour part, but not all hours have data, so there are some gaps.  I'd like to display every hour, regardless of whether or not there's data.</p>
  <p>Here's a sample query:</p>
  <pre class="lang-sql prettyprint-override"><code>SELECT DATEPART(HOUR, DATEADD(HH,-5, CreationDate)) As Hour,
         COUNT(*) AS Count
  FROM Comments
  WHERE UserId = ##UserId##
  GROUP BY DATEPART(HOUR, DATEADD(HH,-5, CreationDate))
  </code></pre>
  <p>My thought was to Join to a table that already had numbers 1 through 24 so that the incoming data would get put in it's place.</p>
  <p>Can I do this with a CTE?</p>
  <pre class="lang-sql prettyprint-override"><code>WITH Hours AS (
     SELECT i As Hour    --Not Sure on this
     FROM [1,2,3...24]), --Not Sure on this
  CommentTimes AS (
     SELECT DATEPART(HOUR, DATEADD(HH,-5, CreationDate)) AS Hour,
            COUNT(*) AS Count
     FROM Comments
     WHERE UserId = ##UserId##
     GROUP BY DATEPART(HOUR, DATEADD(HH,-5, CreationDate))
  )
  SELECT h.Hour, c.Count
  FROM Hours h
  JOIN CommentTimes c ON h.Hour = c.Hour
  </code></pre>
  <p>###Here's a sample <a href="https://data.stackexchange.com/stackoverflow/revision/185460/239532/comments-by-hour" rel="nofollow noreferrer">Query From Stack Exchange Data Explorer</a></p>