Sql-server – Do you know an easy way to generate one record for each hour of the past 12 hours

sql serversql-server-2005

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.

Best Answer

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.

Related Solutions

Sql-server – Determine Gap Records, Fill and Return Result

I think that the approach you came up with is a pretty good one. Here is a proposed simplification that avoids the self-join on dbo.AgentInteractions by using the new SQL Server 2012 LAG function as well as CTEs to distill the logic into a single query (also in SQL Fiddle form).

WITH AgentActions AS (
    -- Sequence the agent actions
    SELECT a.Event,
        LAG(a.TimeStamp, 1) OVER (ORDER BY TimeStamp ASC) AS Previous,
        a.TimeStamp,
        DATEDIFF(HOUR, LAG(a.TimeStamp, 1) OVER (ORDER BY TimeStamp ASC), a.TimeStamp) AS TimeFromLastPoint
    FROM dbo.AgentInteractions a
),
allActionsToReport AS (
    --  Agent action
    SELECT 'Agent' AS Origin, a.Event, a.TimeStamp
    FROM AgentActions a
    UNION ALL
    -- Any management actions during a gap of > 5 hours between agent interactions
    SELECT 'Management' AS Origin, m.event, m.TimeStamp
    FROM AgentActions a
    JOIN ManagementInteractions m
        ON m.TimeStamp BETWEEN a.Previous AND a.TimeStamp
    WHERE a.TimeFromLastPoint > 5
)
-- For all actions (with gaps already filled), perform final sequencing
SELECT a.Origin,
    ROW_NUMBER() OVER (ORDER BY a.TimeStamp ASC) AS Sequence,
    a.Event,
    LAG(a.TimeStamp, 1) OVER (ORDER BY TimeStamp ASC) AS Previous,
    a.TimeStamp,
    ISNULL(DATEDIFF(HOUR, LAG(a.TimeStamp, 1) OVER (ORDER BY TimeStamp ASC), a.TimeStamp), 0) AS TimeFromLastPoint
FROM allActionsToReport a

From taking a look at the query plan for this single-query version, it does exactly what you'd expect: sort the agent actions by timestamp, fill in the gaps by accessing the management actions, and then sort this combined set of resulting actions in order to do the final sequencing:

enter image description here

Sql-server – SQL to choose a price between two points in time

Quick-and-dirty, not tested on a live instance.

For each row of Event data we need one row from Price - the one which happened most recently, but before the Event's timestamp. TSQL supports the top 1 .. order by notation. By embedding the Price lookup as a sub-query in a SELECT list it will be executed once per row in Event. Predicating Price on Event's values will ensure the most recent is returned. Something like this:

select
    e.event_id,
    e.event_time,
    ( select top 1     -- return one value
        p.price
      from Prices as p
      where p.price_time <= e.event_time -- ensure the price change happened at or before the event
      order by p.price_time desc  -- top ensure "top 1" picks the price with the gretest i.e. most recent, timestamp
    ) as price
from Events as e;

The sub-query will run once per row in Events, so performance may be compromised for very large sets. Make sure there are indexes on Price.price_time.

Related Question