Do you know an easy way to generate one record for each hour of the past 12 hours?

Question

I have a report that shows the count of events for the past 12 hours, grouped by the hour. Sounds easy enough, but what I am struggling with is how to include records that cover the gaps.

Here is an example table:

Event
(
  EventTime datetime,
  EventType int
)

Data looks like this:

  '2012-03-08 08:00:04', 1
  '2012-03-08 09:10:00', 2
  '2012-03-08 09:11:04', 2
  '2012-03-08 09:10:09', 1
  '2012-03-08 10:00:17', 4
  '2012-03-08 11:00:04', 1

I need to create a result set that has one record for every hour of the past 12 hours, regardless of there being events during that hour or not.

Assuming the current time is '2012-03-08 11:00:00', the report would show (roughly):

Hour  EventCount
----  ----------
23    0
0     0
1     0
2     0
3     0
4     0
5     0
6     0
7     0
8     1
9     3
10    1

I came up with a solution that uses a table that has one record for every hour of the day. I managed to get the results I was looking for using a UNION and some convoluted case logic in the where clause, but I was hoping somebody had a more elegant solution.

Answer 1

For SQL Server 2005+ you can generate those 12 records very easily with a loop ar a recursive CTE. Here is an example of a recursive CTE:

DECLARE @Date DATETIME
SELECT @Date = '20120308 11:00:00'

;WITH Dates AS
(
    SELECT DATEPART(HOUR,DATEADD(HOUR,-1,@Date)) [Hour], DATEADD(HOUR,-1,@Date) [Date], 1 Num
    UNION ALL
    SELECT DATEPART(HOUR,DATEADD(HOUR,-1,[Date])), DATEADD(HOUR,-1,[Date]), Num+1
    FROM Dates
    WHERE Num <= 11
)
SELECT [Hour], [Date]
FROM Dates

Then you just nedd to join it with your events table.

Answer 2

Tally tables can be used for things like this. They can be very efficient. Create the tally table below. I created the tally table with only 24 rows for your example, but you can create it with however many you want to suit other purposes.

SELECT TOP 24 
        IDENTITY(INT,1,1) AS N
   INTO dbo.Tally
   FROM Master.dbo.SysColumns sc1,
        Master.dbo.SysColumns sc2

--===== Add a Primary Key to maximize performance
  ALTER TABLE dbo.Tally
    ADD CONSTRAINT PK_Tally_N 
        PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100

I assumed your table was called dbo.tblEvents, run the query below. I believe this is what you're looking for:

SELECT t.n, count(e.EventTime)
FROM dbo.Tally t
LEFT JOIN dbo.tblEvent e  on t.n = datepart(hh, e.EventTime)
GROUP BY t.n
ORDER BY t.n

I believe credit goes to the following links, I believe this is where I first came across this:

http://www.sqlservercentral.com/articles/T-SQL/62867/

http://www.sqlservercentral.com/articles/T-SQL/74118/

Answer 3

You'll need to RIGHT JOIN your data with a query returning one record for every hour you need.

See this for a couple ways to get a row numbers which you could then subtract as hours from the current time.

In Oracle a hierarchical query on dual will generate rows:

SELECT to_char(sysdate-level/24,'HH24') FROM dual CONNECT BY Level <=24;