求助：SQL Server中重叠工时工单的工时按比例分配方法

在SQL Server 2019中，需实现**"工时工单（labor tickets）"的工时分配逻辑：每个工单包含"签到时间（clock in）"和"签退时间（clock out）"**，当多个工单时间重叠时，需将重叠时段的工时按参与重叠的工单数量比例分配给各工单。

1. 单工单（无重叠） #

2. 双工单（完全重叠） #

3. 三工单（完全重叠） #

4. 双工单（部分重叠） #

重叠时段的工时在工单间按比例分配。

5. 三工单（复杂重叠） #

手动计算过程 #

• 6:00 AM-7:00 AM：3个工单，工时为 1 hour / 3 = 0.33
• 5:30 AM-6:00 AM：2个工单，工时为 0.5 hours / 2 = 0.25
• 6:00 AM-7:00 AM：3个工单，工时为 1/3 = 0.33
• 7:00 AM-7:30 AM：1个工单，工时为 0.5/1 = 0.5
• 5:00 AM-7:00 AM：
  • 5:00 AM-5:30 AM：1个工单，工时为 0.5 hours / 1 = 0.5
  • 5:30 AM-6:00 AM：2个工单，工时为 0.5 hours / 2 = 0.25
  • 6:00 AM-7:00 AM：3个工单，工时为 1 hour / 3 = 0.33

希望编写一个存储过程，每次仅计算单条工单的工时，无需处理整体递归逻辑。曾尝试用（当前工单时长）除以（总重叠时长）的比例计算，但在部分重叠场景下效果不佳。最坏情况下可通过循环将时间拆分为"时段块"，计算每个块的分配系数后累加，但更希望找到简洁的数学公式实现。

复杂三工单示例 #

DECLARE @LaborTickets TABLE (
    TicketID INT IDENTITY(1,1),
    StartTime DATETIME,
    EndTime DATETIME
);

INSERT INTO @LaborTickets (StartTime, EndTime)
VALUES
    ('2025-02-04 06:00:00', '2025-02-04 07:00:00'),
    ('2025-02-04 05:30:00', '2025-02-04 7:30:00'),
    ('2025-02-04 05:00:00', '2025-02-04 07:00:00');

预期返回结果 #

StartTime           EndTime             Hours
------------------  ------------------  -------------------
2025-02-04 6:00 AM  2025-02-04 7:00 AM  0.33333333333333333
2025-02-04 5:30 AM  2025-02-04 7:30 AM  1.08333333333333333
2025-02-04 5:00 AM  2025-02-04 7:00 AM  1.08333333333333333

3 row(s) affected

多工单精确时间示例 #

实际数据精确到秒，最多可能有20个重叠工单，示例代码如下：

DECLARE @LaborTickets TABLE (
    TicketID INT IDENTITY(1,1),
    StartTime DATETIME,
    EndTime DATETIME
);

INSERT INTO @LaborTickets (StartTime, EndTime)
VALUES
    ('2025-02-04 06:01:13', '2025-02-04 07:00:00'),
    ('2025-02-04 06:01:21', '2025-02-04 07:01:00'),
    ('2025-02-04 06:01:25', '2025-02-04 07:01:30'),
    ('2025-02-04 06:01:29', '2025-02-04 07:01:50'),
    ('2025-02-04 06:01:35', '2025-02-04 07:01:00'),
    ('2025-02-04 06:01:50', '2025-02-04 07:02:00');

内容的提问来源于stack exchange，提问作者Macrobb