如何基于条件累加行数据实现连续工时计算（T-SQL）

需求说明 #

• 若员工两次打卡的间隔小于90分钟（1.5小时），则将对应工时累加；间隔超过90分钟则不累加。
• 数据集已按EmployeeID和punch_start升序生成id字段。

示例数据集 #

select * 
into #temp
from 
(values
(1, 100001, '2021-12-12 23:31', '2021-12-12 23:59', '2021-12-13 00:00', 1,      0.47,   'solo/add'),
(2, 100001, '2021-12-13 00:00', '2021-12-13 03:07', '2021-12-13 03:37', 30,     3.12,   'solo/add'),
(3, 100001, '2021-12-13 03:37', '2021-12-13 07:07', '2021-12-13 23:17', 970,    3.5,    'no add'),
(4, 100001, '2021-12-13 23:17', '2021-12-13 23:59',  NULL,              NULL,   0.7,    'solo/add'),
(5, 100003, '2021-12-12 05:50', '2021-12-12 11:00', '2021-12-12 11:30', 30,     5.17,   'solo/add'),
(6, 100003, '2021-12-12 11:30', '2021-12-12 14:25', '2021-12-13 05:51', 926,    2.92,   'no add'),
(7, 100003, '2021-12-13 05:51', '2021-12-13 11:05', '2021-12-13 11:36', 31,     5.23,   'solo/add'),
(8, 100003, '2021-12-13 11:36', '2021-12-13 14:25',   NULL,             NULL,   2.81,   'solo/add')
)
t1
(id, EmployeeID, punch_start, punch_end, next_punch_start, MinuteDiff,  punch_hr, Decide)

预期结果 #

存在两处连续工时累加：

• 0.47 + 3.12 = 3.59
• 5.23 + 2.81 = 8.04

T-SQL解决方案 #

方案1：输出每个连续时段的总工时 #

该方案按员工分组，输出每个连续打卡时段的起始、结束时间及总工时：

WITH GroupedPunches AS (
    SELECT 
        *,
        -- 生成分组标识：当前记录与上一条间隔超过90分钟时，开启新分组
        SUM(CASE WHEN LAG(MinuteDiff) OVER (PARTITION BY EmployeeID ORDER BY id) > 90 THEN 1 ELSE 0 END) 
            OVER (PARTITION BY EmployeeID ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) + 1 AS GroupID
    FROM #temp
)
SELECT 
    EmployeeID,
    MIN(punch_start) AS continuous_start,
    MAX(ISNULL(punch_end, next_punch_start)) AS continuous_end,
    ROUND(SUM(punch_hr), 2) AS total_continuous_hr
FROM GroupedPunches
GROUP BY EmployeeID, GroupID
ORDER BY EmployeeID, continuous_start;

方案2：保留每条打卡记录并显示累加值 #

该方案保留原始记录的所有字段，同时显示当前记录所在连续时段的累计工时：

WITH GroupedPunches AS (
    SELECT 
        *,
        SUM(CASE WHEN LAG(MinuteDiff) OVER (PARTITION BY EmployeeID ORDER BY id) > 90 THEN 1 ELSE 0 END) 
            OVER (PARTITION BY EmployeeID ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) + 1 AS GroupID
    FROM #temp
)
SELECT 
    id,
    EmployeeID,
    punch_start,
    punch_end,
    punch_hr,
    ROUND(SUM(punch_hr) OVER (PARTITION BY EmployeeID, GroupID), 2) AS accumulated_hr
FROM GroupedPunches
ORDER BY id;

逻辑说明 #

1. 分组标识生成：使用LAG()函数获取当前记录的上一条打卡间隔MinuteDiff，若间隔超过90分钟则标记为新分组的起点，通过累加这些标记生成每个记录的GroupID。
2. 工时累加：利用SUM()窗口函数，按EmployeeID和GroupID分组累加punch_hr，得到连续时段的总工时。
3. 结果格式化：用ROUND()函数保留两位小数，匹配预期结果的精度。

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