Counting people present at each hour

Question

Hey I've got this table:

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 know the number of ID (persons) present at each hour:

how many people were present at 10:00, at 11:0, at 12:00 etc..

Time                            Num_Of_ID_Present
----------------------------------------------------------
2022-06-13 09:00                 2
2022-06-13 10:00                 3
2022-06-13 11:00                 3
2022-06-13 12:00                 3
2022-06-13 13:00                 3
2022-06-13 14:00                 2
2022-06-13 15:00                 2 
.
.
.
2022-06-13 18:00                 2
2022-06-13 19:00                 1
2022-06-13 20:00                 1

someone here gave this code but I think it counts all the people present between 09:00 and 10:00 and returns it in 10:00... I want the people present exactly at 10:00.

If someone came in 09:01 and left at 09:59 I don't want to count him. can someone here help me with a better code?

thanks!

 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, 60, [Time])
from   rcte
where  [Time] < @en
)

select *
from   rcte r
   cross apply
   (
       select cnt = count(*)
       from   Present p
       where  p.BeginDate <= dateadd(minute, 60, r.[Time]) 
       and    p.ExitDate  >= r.[Time]
   ) c

Answer 1

When approaching a problem like this (and depending on the volume of data), I normally create additional columns to adjust for business logic being applied to the original input columns, in this case, BeginDate and ExitDate and create two new columns AdjBeginDate and AdjExitDate. This might help you visualise the problem a little better.

drop table if exists #tmpPopulation;
GO


create table #tmpPopulation
(
    IdNum           int
    , BeginDate     datetime2(0)
    , ExitDate      datetime2(0)
    , AdjBeginDate  datetime2(0)
    , AdjExitDate   datetime2(0)
    , ExcludeEntry  char(1)
)
;

insert into #tmpPopulation
(
    IdNum
    , BeginDate
    , ExitDate
)
values
(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')
;

update  #tmpPopulation
set     AdjBeginDate = case
                        when datepart(minute, BeginDate) > 0
                        then cast(convert(varchar(13), dateadd(hour, 1, BeginDate), 120) + ':00:00' as datetime2(0))
                        else cast(convert(varchar(13), dateadd(hour, 0, BeginDate), 120) + ':00:00' as datetime2(0))
                    end
        , AdjExitDate =  cast(convert(varchar(13), dateadd(hour, 0, ExitDate), 120) + ':00:00' as datetime2(0))
        , ExcludeEntry = case 
                            when cast(BeginDate as date) = cast(ExitDate as date) and datepart(hour, BeginDate) = datepart(hour, ExitDate)
                            then 'Y'
                            else 'N'
                        end
from    #tmpPopulation as tp
;

declare @maxExitDate datetime2(0) = (select max(ExitDate) from #tmpPopulation)
;


with cte as
(
    select  min(AdjBeginDate) as CurrentDt from #tmpPopulation 
    union all
    select  dateadd(hour, 1, CurrentDt)
    from    cte
    where   CurrentDt <= @maxExitDate
)
select      cte.CurrentDt
            , count(1) as NumVisitors
from        cte
inner join  #tmpPopulation as tp
on          cte.CurrentDt between tp.AdjBeginDate and tp.AdjExitDate
and         tp.ExcludeEntry = 'N'
group by    cte.CurrentDt
;

I have also added a piece to cater for cater for any excluded entries whereby a person enters and leaves within the same hour. By adjusting the start and end times, it becomes much easier to feed these into the CTE for generating all the hourly times between the min(BeginDate) to max(ExitDate).

Answer 2

change the apply query to

   cross apply
   (
       select cnt = count(*)
       from   Present p
       where  r.[Time] between p.BeginDate and p.Exitdate
   ) c