时间戳

首页 > 解决方案 > SQL中根据时长和数量生成时隙

tsql - SQL中根据时长和数量生成时隙

问题描述

我有一个表，由于某种原因存储这样的轮播：

Rota      | Date_Start              | Position | Duration | Quantity | Rota_Slot_Type
----------+-------------------------+----------+----------+----------+---------------
372387412 | 2020-04-12 08:00:00.000 | 1        | 15       | 15       | Not available
372387412 | 2020-04-12 08:00:00.000 | 2        | 15       | 1        | Support Slot
372387412 | 2020-04-12 08:00:00.000 | 3        | 30       | 1        | Lunch Break
372387412 | 2020-04-12 08:00:00.000 | 4        | 15       | 13       | Not available
372387412 | 2020-04-12 08:00:00.000 | 5        | 15       | 1        | Support Slot
372387412 | 2020-04-12 08:00:00.000 | 6        | 30       | 1        | Lunch Break
372387412 | 2020-04-12 08:00:00.000 | 7        | 15       | 12       | Not available
372387412 | 2020-04-12 08:00:00.000 | 8        | 15       | 1        | Support Slot
372387412 | 2020-04-12 08:00:00.000 | 9        | 15       | 1        | Not available

更改表不是一种选择。

我已经生成了这个：

ID_Rota   | Date_Start              | RowNumber | Position | Number | Duration | Quantity | Rota_Slot_Type
----------+-------------------------+-----------+----------+--------+----------+----------+---------------
372387412 | 2020-04-12 08:00:00.000 | 1         | 1        | 1      | 15       | 15       | Not available
372387412 | 2020-04-12 08:00:00.000 | 2         | 1        | 2      | 15       | 15       | Not available
372387412 | 2020-04-12 08:00:00.000 | 3         | 1        | 3      | 15       | 15       | Not available
372387412 | 2020-04-12 08:00:00.000 | 4         | 1        | 4      | 15       | 15       | Not available
372387412 | 2020-04-12 08:00:00.000 | 5         | 1        | 5      | 15       | 15       | Not available
372387412 | 2020-04-12 08:00:00.000 | 6         | 1        | 6      | 15       | 15       | Not available
372387412 | 2020-04-12 08:00:00.000 | 7         | 1        | 7      | 15       | 15       | Not available
372387412 | 2020-04-12 08:00:00.000 | 8         | 1        | 8      | 15       | 15       | Not available
372387412 | 2020-04-12 08:00:00.000 | 9         | 1        | 9      | 15       | 15       | Not available
372387412 | 2020-04-12 08:00:00.000 | 10        | 1        | 10     | 15       | 15       | Not available
372387412 | 2020-04-12 08:00:00.000 | 11        | 1        | 11     | 15       | 15       | Not available
372387412 | 2020-04-12 08:00:00.000 | 12        | 1        | 12     | 15       | 15       | Not available
372387412 | 2020-04-12 08:00:00.000 | 13        | 1        | 13     | 15       | 15       | Not available
372387412 | 2020-04-12 08:00:00.000 | 14        | 1        | 14     | 15       | 15       | Not available
372387412 | 2020-04-12 08:00:00.000 | 15        | 1        | 15     | 15       | 15       | Not available
372387412 | 2020-04-12 08:00:00.000 | 16        | 2        | 1      | 15       | 1        | Support Slot
372387412 | 2020-04-12 08:00:00.000 | 17        | 3        | 1      | 30       | 1        | Lunch Break
372387412 | 2020-04-12 08:00:00.000 | 18        | 4        | 1      | 15       | 13       | Not available
372387412 | 2020-04-12 08:00:00.000 | 19        | 4        | 2      | 15       | 13       | Not available
372387412 | 2020-04-12 08:00:00.000 | 20        | 4        | 3      | 15       | 13       | Not available

（前 20 行，共有 46 行）这是使用以下 SQL 生成的：

select
    rs.ID_Rota,
    rs.Date_Start,
    row_number() over (partition by rs.Date_Start, rs.ID_Rota order by rs.Position, n.Number) as [RowNumber],
    rs.Position,
    n.Number,
    rs.Duration,
    rs.Quantity,
    rs.Rota_Slot_Type
from dbo.RotaSlots as [rs]
    cross apply
        (
            select top (rs.Quantity)
                n.Number + 1 as [Number]
            from dbo.Numbers as [n]
        ) as [n]

我正在努力的部分是为每一行生成一个 Slot_Start 列。我的预期输出是：

ID_Rota   | Date_Start              | Slot_Start              | RowNumber | Position | Number | Duration | Quantity | Rota_Slot_Type
----------+-------------------------+-------------------------+-----------+----------+--------+----------+----------+---------------
372387412 | 2020-04-12 08:00:00.000 | 2020-04-12 08:00:00.000 | 1         | 1        | 1      | 15       | 15       | Not available
372387412 | 2020-04-12 08:00:00.000 | 2020-04-12 08:15:00.000 | 2         | 1        | 2      | 15       | 15       | Not available
372387412 | 2020-04-12 08:00:00.000 | 2020-04-12 08:30:00.000 | 3         | 1        | 3      | 15       | 15       | Not available
372387412 | 2020-04-12 08:00:00.000 | 2020-04-12 08:45:00.000 | 4         | 1        | 4      | 15       | 15       | Not available
372387412 | 2020-04-12 08:00:00.000 | 2020-04-12 09:00:00.000 | 5         | 1        | 5      | 15       | 15       | Not available
372387412 | 2020-04-12 08:00:00.000 | 2020-04-12 09:15:00.000 | 6         | 1        | 6      | 15       | 15       | Not available
372387412 | 2020-04-12 08:00:00.000 | 2020-04-12 09:30:00.000 | 7         | 1        | 7      | 15       | 15       | Not available
372387412 | 2020-04-12 08:00:00.000 | 2020-04-12 09:45:00.000 | 8         | 1        | 8      | 15       | 15       | Not available
372387412 | 2020-04-12 08:00:00.000 | 2020-04-12 10:00:00.000 | 9         | 1        | 9      | 15       | 15       | Not available
372387412 | 2020-04-12 08:00:00.000 | 2020-04-12 10:15:00.000 | 10        | 1        | 10     | 15       | 15       | Not available
372387412 | 2020-04-12 08:00:00.000 | 2020-04-12 10:30:00.000 | 11        | 1        | 11     | 15       | 15       | Not available
372387412 | 2020-04-12 08:00:00.000 | 2020-04-12 10:45:00.000 | 12        | 1        | 12     | 15       | 15       | Not available
372387412 | 2020-04-12 08:00:00.000 | 2020-04-12 11:00:00.000 | 13        | 1        | 13     | 15       | 15       | Not available
372387412 | 2020-04-12 08:00:00.000 | 2020-04-12 11:15:00.000 | 14        | 1        | 14     | 15       | 15       | Not available
372387412 | 2020-04-12 08:00:00.000 | 2020-04-12 11:30:00.000 | 15        | 1        | 15     | 15       | 15       | Not available
372387412 | 2020-04-12 08:00:00.000 | 2020-04-12 11:45:00.000 | 16        | 2        | 1      | 15       | 1        | Support Slot
372387412 | 2020-04-12 08:00:00.000 | 2020-04-12 12:15:00.000 | 17        | 3        | 1      | 30       | 1        | Lunch Break
372387412 | 2020-04-12 08:00:00.000 | 2020-04-12 12:30:00.000 | 18        | 4        | 1      | 15       | 13       | Not available
372387412 | 2020-04-12 08:00:00.000 | 2020-04-12 12:45:00.000 | 19        | 4        | 2      | 15       | 13       | Not available
372387412 | 2020-04-12 08:00:00.000 | 2020-04-12 13:00:00.000 | 20        | 4        | 3      | 15       | 13       | Not available

第一个块相对简单 -(RowNumber - 1) * Duration为我提供位置 1 下所有行的 Slot_Start。当您将块切换到位置 2、位置 3 等时，它会下降。

任何帮助都将不胜感激。

标签： tsqlsql-server-2012

解决方案

获取 Slot_Start 时间的最简单方法是

  • 对行进行排序（就像您使用 RowNumber 一样）
  • 对所有先前行的分钟持续时间进行累积总和（运行总计）
  • 将此累积总和添加到 Start_Datetime

换句话说 -(rownumber - 1) * Duration你得到所有相关前行的 Durations 总和，而不是计算 。

您可以使用如下表达式来计算 Slot_Start

DATEADD(minute, SUM(Duration) OVER (PARTITION BY ID_Rota, Date_Start ORDER BY RowNumber) - Duration, Date_Start) AS Slot_Start

请注意，您可能需要将代码放入子查询或 CTE 中，以便计算 RowNumber，或者您可以将 ROW_NUMBER 表达式等合并到同一组计算中。

您也可以在窗口函数中显式使用 ROWS，除非您需要考虑第一行，例如，

ISNULL(DATEADD(minute, SUM(Duration) OVER (PARTITION BY ID_Rota, Date_Start ORDER BY RowNumber ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), Date_Start), Date_Start) AS Slot_Start2

这是一个db<>fiddle，其中的数据类似于您上面的数据（在意识到您正在做不同的事情之前，我开始走上一条道路；但是，Duration 的累积总和应该仍然有效）。

推荐阅读