Блог

Я пытаюсь рассчитать количество занятых шкафчиков в раздевалке. Набор данных выглядит следующим образом:

 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
 

Моим идеальным результатом будет количество занятых шкафчиков в час:

 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
 

Я могу вручную вычислить это число в Excel («количество шкафчиков для регистрации в течение часа» «количество шкафчиков, используемых в течение часа» — «количество выездов в течение часа»), но я не могу вычислить его в SQL-сервер. Я понятия не имею, как получить количество используемых шкафчиков в приведенном выше уравнении.

Под «занятым» я подразумеваю шкафчик, который в настоящее время используется в течение часа. Например, если пользователь A зарегистрирует (начнет использовать) шкафчик в 8:30 утра и выпишется (освободит шкафчик и прекратит его использование) в 10:30 утра, то для пользователя A один «занятый» шкафчик будет учитываться в течение 8:00 — 8:59 утра, 9: 00-9:59 и 10:00-10:59 соответственно.

Вам нужно сгенерировать часы. Затем вы можете выполнить вычисление. Если вы хотите в любое время в течение часа:

 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;