SQL: how do you calculate occupancy/ number of in-use for a facility?

I am trying to calculate the number of occupied lockers in the changing room. The dataset goes like this:

Locker ID   Check-in Datetime   Check-out Datetime  Member ID
1           2/18/2021 08:15am   2/18/2021 10:20am   A
2           2/18/2021 09:00am   2/18/2021 09:30am   B
3           2/18/2021 09:15am   2/18/2021 11:05am   C
4           2/18/2021 10:25am   2/18/2021 11:50am   D

My ideal output will be the number of occupied lockers per hour:

Date        Hour                No. of Occupied Lockers
2/18/2021   8:00am - 9:00am     1
2/18/2021   9:00am - 10:00am    3
2/18/2021   10:00am - 11:00am   3
2/18/2021   11:00am - 12:00pm   1

I am able to manually calculate the number in excel (‘no. of check-in lockers during the hour’ + ‘no. of lockers in-use during the hour’ – ‘no. of check-out during the hour’), but I am not able to calculate it in SQL-server. I have no idea how to retrieve the no. of lockers in-use in the above captioned equation.

By “occupied”, I mean the locker that is currently being used in the hour. For example, if User A check-in (start using) a locker at 8:30am and check-out (release the locker and stop using it) at 10:30am, then for User A, one “occupied” locker will be counted during 8:00am – 8:59am, 9:00am-9:59am and 10:00am-10:59am respectively.

Answer

You need to generate the hours. Then you can do the calculation. If you want any time during the hour:

with dt as (
      select convert(datetime, '2021-02-18 08:00:00') as dt
      union all
      select dateadd(hour, 1, dt)
      from dt
      where dt < '2012-02-18 11:00:00'
     )
select dt,
       (select count(*)
        from t
        where t.checkin < dateadd(hour, 1, dt.dt) and
              t.checkout >= dt.dt
       ) as cnt
from dt;