Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
4  
This is a classic use case for a numbers table. For such a small number of rows, you could get away with generating the table on the fly using a CTE. –  Nick Chammas Mar 8 '12 at 18:34
    
After searching a lot on stackoverflow to reach 30 char and help someone giving the link. There is an answer here: stackoverflow.com/a/22075321 –  s4f56d4sf Feb 27 at 17:17
add comment

3 Answers 3

up vote 15 down vote accepted

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.

share|improve this answer
2  
I found this just after you posted. explainextended.com/2009/10/21/… It indicates that using a CTE for this purpose is less efficient than a stored table. Is this true? As Nick said, it probably doesn't matter for this case, but... –  Leigh Riffel Mar 8 '12 at 18:42
3  
I think that it would make a difference with a bigger number of rows, if you need 12 records, then there will be no performance hit at all –  Lamak Mar 8 '12 at 18:44
    
I love it. CTE's are moving up my list of new favorite things. –  datagod Mar 8 '12 at 18:44
3  
@JeffModen absolutely horrible for performance even for a piddly 12 rows?, I think that you may be exaggerating just a little. I used it because is easy and an alternative for the user. If you think that is a wrong answer or unuseful, then by all means, downvote it –  Lamak Jun 4 '13 at 13:28
3  
@JeffModen please feel free to post a better answer or suggest an edit to this one. otherwise this just seems like tilting at windmills. i respect your work and would rather see you post something valuable that justifies your stated concern. –  swasheck Jul 5 '13 at 20:46
show 3 more comments

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/

share|improve this answer
    
+1 but semantically it's a table of numbers, not a table of tallies. –  Aaron Bertrand Jul 8 '13 at 12:22
    
Yeah for number tables - they're all the rage. –  RThomas Sep 11 '13 at 21:04
add comment

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;
share|improve this answer
    
It is the "query returning one record for every hour" that I am having problems with. Just trying to figure out a way to generate 12 (or 24) records with every hour of the past 12 (or 24) hours. –  datagod Mar 8 '12 at 18:17
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.