SQL:如何计算设施的占用/在用数量？

Question

我在试着计算更衣室里被占用的储物柜的数量。数据集如下所示：

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-server中计算它。我不知道怎么找回那个号码。在上述标题方程中使用的储物柜。

所谓“占用”，我指的是目前正在使用的一个小时内的储物柜。例如，如果用户A在上午8:30登记(开始使用)一个储物柜，并在上午10:30退房(释放储物柜并停止使用)，那么对于用户A，将分别在上午8:00-8:59、上午9:00-9:59和上午10:00-10:59分别计算一个“被占用”的储物柜。

Answer 1

你需要生成时间。然后你就可以做计算了。如果你想在一小时内有任何时间：

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;