[SOLVED] Select Consecutive Numbers in SQL

Select Consecutive Numbers in SQL

This feels simple, but I can't find an answer anywhere.
I'm trying to run a query by time of day for each hour. So I'm doing a Group By 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.

Here's a sample query:

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))

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.

Can I do this with a CTE?

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

###Here's a sample Query From Stack Exchange Data Explorer

Solution

You can use a recursive query to build up a table of whatever numbers you want. Here we stop at 24. Then left join that to your comments to ensure every hour is represented. You can turn these into times easily if you wanted. I also changed your use of hour as a column name as it is a keyword.

;with dayHours as (
    select 1 as HourValue
    union all select hourvalue + 1
    from dayHours
    where hourValue < 24
)
,
CommentTimes As (
       SELECT DATEPART(HOUR, DATEADD(HH,-5, CreationDate)) As HourValue,
              COUNT(*) AS Count
       FROM Comments
       WHERE UserId = ##UserId##
       GROUP BY DATEPART(HOUR, DATEADD(HH,-5, CreationDate)))
SELECT h.Hour, c.Count
FROM dayHours h
left JOIN CommentTimes c ON h.HourValue = c.HourValue