时间戳

首页 > 解决方案 > SQL：您如何计算设施的占用率/使用中的数量？

sql - SQL：您如何计算设施的占用率/使用中的数量？

问题描述

我正在尝试计算更衣室中占用的储物柜数量。数据集是这样的：

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 期间。

标签： sqlsql-servercountcounterduration

解决方案

您需要生成小时数。然后就可以进行计算了。如果您想在一小时内的任何时间：

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;

推荐阅读