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