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.

## Advertisement

## 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;

**9**People found this is helpful