SQL Query for Rolling Counts Per Hour-CodePudding

I need a SQL (SQL Server) query that returns a count of current events per hour.

I have a table of events that all have a start and end datetime and I want a count of events that are ongoing or complete by hour.

Sample data:

ID    StartDateTime          EndDateTime
------------------------------------------------
1     2022-07-03 08:35:22    2022-07-03 09:22:22
2     2022-07-03 11:35:22    2022-07-03 11:51:22
3     2022-07-03 11:56:22    2022-07-03 15:45:22
4     2022-07-03 12:31:22    2022-07-03 13:22:22
5     2022-07-03 13:44:22    2022-07-03 17:33:22
6     2022-07-03 14:11:22    2022-07-03 21:22:22

This means an event is counted in each hour if it has an end datetime in that hour, or after it. Events from previous hours carry over so its a rolling count. Events are NOT counted if they haven't started yet, or have ended.

Something like this:

Hour                  NumEvents
--------------------------------
00:00:01 - 01:00:00   0
01:00:01 - 02:00:00   0
02:00:01 - 03:00:00   0
03:00:01 - 04:00:00   2
04:00:01 - 05:00:00   3
05:00:01 - 06:00:00   7
06:00:01 - 07:00:00   11
07:00:01 - 08:00:00   15
08:00:01 - 09:00:00   19
9:00:01 - 10:00:00    27
10:00:01- 11:00:00    15

etc. all the way from 0 (12 am) to 23 (11pm)

For example:

Start: 09:05:00 and End: 17:00:00. This would be counted in hours 9, 10, 11, 12, 13, 14, 15, and 16
Start: 05:03:00 to 05:20:33 would only be count in hour 5.
Start: 05:03:00 to 06:27:33 would be counted in hours 5 and 6.

Desired output:

Hour       NumEvents
--------------------
00:00:00   0
01:00:00   0
02:00:00   0
03:00:00   2
04:00:00   3
05:00:00   7
06:00:00   11
07:00:00   15
08:00:00   19
9:00:00    27
10:00:00   15

I can do a count in each hour like so but I can't work out how to do perform the rolling count:

SELECT 
    COUNT(ID), DATEPART(HOUR, StartDateTime)) AS EventHour 
FROM 
    Events 
GROUP BY 
    StartDateTime

Any help would be appreciated.

CodePudding user response：

use a tally table if you have one. If you don't have one, you should consider build one. Alternatively you can use a recursive cte or cross join to generate one on the fly.

Once you have the tally table, the rest is easy.

select  dateadd(hour, datediff(hour, 0, start_dt)   n, 0) as [Hour], 
        count(*) as [Count]
from    tbl t
        inner join tally n on  n.n  >= 0
                           and n.n  <= datediff(hour, start_dt, end_dt)
group by dateadd(hour, datediff(hour, 0, start_dt)   n, 0)

CodePudding user response：

Try the following recursive query:

with cte as 
(
    select id,st,en
    from MyTable where format(st,'yyyy-MM-dd')='2022-07-03'
    Union All
    select id,DateAdd(Hour, 1 ,St),en from cte
    where DateAdd(Hour, 1 ,St)<=en
),
cte2 as 
(
    select 0 as Events, format(cast('00:00:00' as datetime),'HH:mm:ss') as Hours
    union all 
    select 0,format(dateadd(hour,1,Hours),'HH:mm:ss') from cte2
    where dateadd(hour,1,Hours) <='23:00:00'
)

select coalesce(D.Events,0) as Events ,T.Hours from 
(
    select count(id) as Events, format(st,'HH:00:00') as Hours from cte 
    group by format(st,'HH:00:00')
) D
right join cte2 T
on D.Hours=T.Hours

See a demo from here.