Counting number of persons present each 15 min in SQL-CodePudding

Ive got this Table called 'Present' Im using microsoft management SQL:

IdNum                  BeginDate                      Exitdate
-------------------------------------------------------------------------
123                    2022-06-13 09:03               2022-06-13 22:12
633                    2022-06-13 08:15               2022-06-13 13:09
389                    2022-06-13 10:03               2022-06-13 18:12
665                    2022-06-13 08:30               2022-06-13 10:16

I want to get data for each 15 min of the day to know if the person (IdNum) was in the process.

For example: I want to know how many IdNum were in the process between 18:00 and 18:15.

According to the table above I should get: 2

Because only IdNum 123 and IdNum 389 were in the process during those sepecific 15 minutes of the day.

What I want is the help with writing the query that gives me the number of ID that are present in each 15 min between 2 date parameters I will choose:

lets say parameter1 = '2022-06-13 09:00'

and parameter2 = '2022-06-13 18:30'

and then the output:

 Time                            Num_Of_ID_Present
 ----------------------------------------------------------
 2022-06-13 09:00                 2
 2022-06-13 09:15                 3
 2022-06-13 09:30                 3
 2022-06-13 09:45                 3
 2022-06-13 10:00                 3
 2022-06-13 10:15                 4
 2022-06-13 10:30                 3 
 .
 .
 .
 2022-06-13 18:00                 2
 2022-06-13 18:15                 1

CodePudding user response：

Use a tally table if you have one to generate a series of datetime with 15 minutes interval.

Solution below uses a recursive cte to generate the time series. For the count, use CROSS APPLY to perform a count on table Present with condition

where  [BeginDate] <= @EndDateTime
and    [ExitDate]  >= @StartDatetime

here @StartDatetime and @EndDateTime are the datetime range of the 15 mins interval

Complete query:

declare @st datetime = '2022-06-13 09:00',
        @en datetime = '2022-06-13 18:30';
        
with rcte as
(
    select [Time] = @st
    union all
    select [Time] = dateadd(minute, 15, [Time])
    from   rcte
    where  [Time] < @en
)
select *
from   rcte r
       cross apply
       (
           select cnt = count(*)
           from   Present p
           where  p.BeginDate <= dateadd(minute, 15, r.[Time]) 
           and    p.ExitDate  >= r.[Time]
       ) c

db<>fiddle demo