Count hourly event types in SQL Server 2008 and above-CodePudding

I want to count event types each hour in a day within the given start and end date.

The sample table below holds the data, this has data of 2 days but production can have millions of rows ranging over years:

declare @Events table(EventDateTime DateTime2,EventType int)  
INSERT @Events (EventDateTime, EventType) VALUES ('2022-12-21T17:45:08.0000000', 12)       
INSERT @Events (EventDateTime, EventType) VALUES ('2022-12-21T14:07:04.0000000', 51)       
INSERT @Events (EventDateTime, EventType) VALUES ('2022-12-21T17:44:00.0000000', 42)       
INSERT @Events (EventDateTime, EventType) VALUES ('2022-12-20T12:59:27.0000000', 60)       
INSERT @Events (EventDateTime, EventType) VALUES ('2022-12-21T17:44:03.0000000', 11)       
INSERT @Events (EventDateTime, EventType) VALUES ('2022-12-21T17:44:03.0000000', 43)       
INSERT @Events (EventDateTime, EventType) VALUES ('2022-12-21T21:11:23.0000000', 51)       
INSERT @Events (EventDateTime, EventType) VALUES ('2022-12-21T17:45:21.0000000', 51)       
INSERT @Events (EventDateTime, EventType) VALUES ('2022-12-21T17:44:00.0000000', 12)       
INSERT @Events (EventDateTime, EventType) VALUES ('2022-12-21T17:45:13.0000000', 55)       
INSERT @Events (EventDateTime, EventType) VALUES ('2022-12-21T14:11:20.0000000', 51)       
INSERT @Events (EventDateTime, EventType) VALUES ('2022-12-21T17:44:03.0000000', 53)       
INSERT @Events (EventDateTime, EventType) VALUES ('2022-12-20T13:00:21.0000000', 55)       
INSERT @Events (EventDateTime, EventType) VALUES ('2022-12-21T13:00:21.0000000', 51)       
INSERT @Events (EventDateTime, EventType) VALUES ('2022-12-20T13:00:21.0000000', 51)       
INSERT @Events (EventDateTime, EventType) VALUES ('2022-12-21T14:12:24.0000000', 51)       
INSERT @Events (EventDateTime, EventType) VALUES ('2022-12-21T14:14:04.0000000', 60)       
INSERT @Events (EventDateTime, EventType) VALUES ('2022-12-21T14:15:22.0000000', 51)       
INSERT @Events (EventDateTime, EventType) VALUES ('2022-12-21T17:45:08.0000000', 42)       
INSERT @Events (EventDateTime, EventType) VALUES ('2022-12-21T14:15:28.0000000', 51)       
INSERT @Events (EventDateTime, EventType) VALUES ('2022-12-21T17:45:13.0000000', 43)       
INSERT @Events (EventDateTime, EventType) VALUES ('2022-12-21T14:12:56.0000000', 51)       
INSERT @Events (EventDateTime, EventType) VALUES ('2022-12-21T17:43:48.0000000', 11)       
INSERT @Events (EventDateTime, EventType) VALUES ('2022-12-21T14:14:14.0000000', 51)       
INSERT @Events (EventDateTime, EventType) VALUES ('2022-12-20T12:59:27.0000000', 60)       
INSERT @Events (EventDateTime, EventType) VALUES ('2022-12-20T04:59:08.0000000', 42)       
INSERT @Events (EventDateTime, EventType) VALUES ('2022-12-21T14:09:10.0000000', 51)       
INSERT @Events (EventDateTime, EventType) VALUES ('2022-12-20T13:00:21.0000000', 43)       
INSERT @Events (EventDateTime, EventType) VALUES ('2022-12-20T04:59:12.0000000', 61)       
INSERT @Events (EventDateTime, EventType) VALUES ('2022-12-21T14:08:59.0000000', 60)

These are all possible event types the Events table can have:

declare @AllEventTypes table(EventType int)
  insert into @AllEventTypes values (12),(20), (21),(22),(30),(31),
                                    (32),(40),(41),(42),(43),(44),
                                    (45),(46),(47),(50),(51),(52),
                                    (53),(54),(55),(56),(57),(58),
                                    (59),(60),(61),(70),(71),(72)

Given Date range:

  DECLARE @StartDate DATETIME2 = '2022-12-20 00:00:01.0000000', 
            @EndDate DATETIME2 = '2022-12-21 23:59:59.0000000'

Example of hours for each event

 declare @AllDates table(Dates DateTime2)
            insert into @AllDates 

        SELECT  TOP (DATEDIFF(Hour, @StartDate, @EndDate)   1)
        Date = DATEADD(Hour, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1, @StartDate)
FROM    sys.all_objects a
        CROSS JOIN sys.all_objects b;
        Select * from @AllDates

The following query returns counts for each event each day within the given min and max date. I want to return counts for each hour everyday(24 hours) so each EventType will either have 0 or more count each hour. If the event type that is in @AllEventTypes does not exist in @Events then returns 0 count for that event for that hour:

  SELECT DATEADD(dd, DATEDIFF(dd, 0, EventDateTime), 0) as Date,
  EventType ,
  Count(EventType) as EventCount
  from  @Events
  where   EventDateTime>=@StartDate and EventDateTime<=@EndDate
  GROUP BY  DATEADD(dd, DATEDIFF(dd, 0, EventDateTime), 0),EventType

Sample Result:

-- Highly appreciate your insights

CodePudding user response：

Try the following, explanation is within the query comments:

/* A recursive CTE to get the 24 hours from '00:00:00' to '23:00:00:' */
with all_day_hours as
(
  select 1 id, Cast('00:00:00' as time(0)) h
  union all
  select id 1, DATEADD(hour, 1, h) 
  from all_day_hours 
  where id<=23
),
/* The following CTE is to join all daily hours to the selected date range,
   and then join the types from the AllEventTypes table.
   this CTE will generate (date - hour - type) for each selected date, 
   each daily hour, and each type.
*/
alltypes_per_datehour as
(
  select CAST(CONCAT(Sel_Dates.dt, ' ', A.h) as datetime2) dt, ET.EventType
  from all_day_hours A cross join
  (
    select distinct CAST(EventDateTime as date) dt
    from @events
    where CAST(EventDateTime as date) between '2022-12-20' and '2022-12-21'
  ) Sel_Dates
  cross join @AllEventTypes ET
)
/* Now, you have all possible combinations of (date, hour, type),
   so you can use a left join to get the required counts.
*/
select DTH.dt as [date], DTH.EventType,
       COUNT(E.EventType) as EventCount
from alltypes_per_datehour DTH left join @Events E
On E.EventDateTime >= DTH.dt and E.EventDateTime < DATEADD(hour, 1, DTH.dt)
   and DTH.EventType = E.EventType
group by DTH.dt, DTH.EventType
order by DTH.dt

See demo.