Counting number of persons present each 15 min in SQL

Question

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

Looks like duplicate for this: https://stackoverflow.com/a/5101424/22683 — Torbjörn Hansson, Jul 04 '22 at 07:08
Hey Torbjörn Hansson Im sorry but i stil lcant understand the solution. It would be great if someone can help me with the start of the code at least.. — KapSht, Jul 04 '22 at 07:46

score 1 · Accepted Answer · answered Jul 04 '22 at 08:15

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

Hey Squirrel! it worked great but just for a single day. when I try to write a whole month or even a week it takes a lot of time and then writes: "The statement terminated. The maximum recursion 100 has been exhausted before statement completion." is there any way to make it possible for me to put 2 parameters with a week apart from each other and it will work? thanks a lot! — KapSht, Jul 04 '22 at 09:40
for recursive cte, the default maximum recursion is 100. You can append at the end of your statement `OPTION (MAXRECURSION 0)` to bypass the limit. Refer to [doc](https://learn.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-ver16). A better solution is to create a permanent tally table. Refer to the link provided in the answer — Squirrel, Jul 04 '22 at 11:39

Counting number of persons present each 15 min in SQL

1 Answers1