SQL Server按周一零点截断跨周班次 统计员工周工作时长

需求规则 #

需基于Bookings表统计员工周度总工作时长，统计规则如下：

• 周度时间边界：每周一零点至下一周周一零点
• 跨边界排班处理：排班记录跨周边界时，需按边界时间截断后，再计算落在统计周期内的有效时长

基础信息 #

表结构与测试数据 #

create table Bookings (ID int IDENTITY(1,1) not null, start datetime, finish datetime, staffId int)

insert into Bookings (start, finish, staffId) values ('2022-06-19 21:00:00', '2022-06-20 07:00:00', 1)
insert into Bookings (start, finish, staffId) values ('2022-06-24 21:00:00', '2022-06-25 07:00:00', 1)
insert into Bookings (start, finish, staffId) values ('2022-06-25 21:00:00', '2022-06-26 07:00:00', 1)
insert into Bookings (start, finish, staffId) values ('2022-06-26 21:00:00', '2022-06-27 07:00:00', 1)

初始查询的错误点 #

初始查询未做跨边界截断，直接按排班原始时长计算：

select *, datediff(MINUTE, start, finish)/60.0
from Bookings
where staffid = 1 and start between '2022-06-19' and '2022-06-27'

针对2022-06-20（周一）至2022-06-27（周一）的统计周期，初始查询返回总时长40小时，不符合业务要求：

• 第一条记录（2022-06-19 21:00至2022-06-20 07:00）跨周起始边界，有效起始时间应为2022-06-20 00:00，有效工时7小时，原始计算多算3小时
• 第四条记录（2022-06-26 21:00至2022-06-27 07:00）跨周结束边界，有效结束时间应为2022-06-27 00:00，有效工时3小时，原始计算多算7小时
• 中间两条记录完全落在统计周期内，每条有效工时10小时，周期内正确总工时应为30小时。

正确实现代码（SQL Server） #

核心思路：先计算每条排班记录对应周的起止边界，再对排班时间做截断，取排班时间和周边界的交集作为有效计算区间，最后汇总时长。

WITH BookingWithWeek AS (
    SELECT
        *,
        -- 计算排班所在周的周一零点，兼容@@DATEFIRST环境配置
        DATEADD(DAY, 1 - (DATEPART(WEEKDAY, start) + @@DATEFIRST - 2) % 7, CAST(start AS DATE)) AS week_start,
        -- 计算对应周的结束点（下周一零点）
        DATEADD(DAY, 8 - (DATEPART(WEEKDAY, start) + @@DATEFIRST - 2) % 7, CAST(start AS DATE)) AS week_end
    FROM Bookings
    WHERE staffId = 1
)
SELECT
    staffId,
    week_start AS stat_week_start,
    week_end AS stat_week_end,
    SUM(
        DATEDIFF(
            MINUTE,
            -- 有效开始时间：取排班开始时间、周开始时间的较晚值
            CASE WHEN start > week_start THEN start ELSE week_start END,
            -- 有效结束时间：取排班结束时间、周结束时间的较早值
            CASE WHEN finish < week_end THEN finish ELSE week_end END
        ) / 60.0
    ) AS total_valid_hours
FROM BookingWithWeek
-- 指定要统计的周起始时间即可，示例统计2022-06-20开始的周
WHERE week_start = '2022-06-20'
GROUP BY staffId, week_start, week_end

逻辑说明 #

• 周起始计算兼容SQL Server的@@DATEFIRST设置，不会因为环境星期起始配置不同导致周一计算错误
• 截断逻辑自动适配三种排班场景：完全在周内、跨周起始、跨周结束，不需要额外写复杂分支判断
• 运行上述测试数据，将返回正确结果：总有效工时30小时。

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