基于工作时间计算SQL Server中服务请求的有效工时

问题分析 #

需要统计的有效工时规则：

• 已完成请求：计算CreatedAt到LastUpdateAt的时长
• 未完成请求：计算CreatedAt到当前时间的时长
• 排除周末（周六、周日）
• 仅统计每日8:00-16:00的工作时段（每日最多8小时）

原代码的问题 #

• CTE中DYN_DATE被固定为CreatedAt，导致统计周末天数的子查询逻辑错误，无法正确计算START_DATE到END_DATE区间内的周末数量
• 总时长减去非工作时长的逻辑忽略了首尾两天的部分工时情况（比如开始时间在工作时段之后、结束时间在工作时段之前）
• 非工作时长的计算方式过于粗糙，未考虑每日非工作时段的实际分布

解决方案 #

以下代码通过分步计算完整工作日工时、首尾两天的部分工时，再排除周末影响，得到准确的有效工时：

WITH RequestDates AS (
    SELECT
        Id,
        CreatedAt AS StartDate,
        ISNULL(LastUpdateAt, GETDATE()) AS EndDate
    FROM Request
    WHERE Id = '14578' -- 移除该条件可计算所有请求
),
WorkDayCalculations AS (
    SELECT
        Id,
        StartDate,
        EndDate,
        -- 计算区间内的完整工作日数量（排除周末）
        DATEDIFF(DAY, StartDate, EndDate) 
        - (DATEDIFF(WEEK, StartDate, EndDate) * 2) 
        - CASE WHEN DATEPART(WEEKDAY, StartDate) IN (6,7) THEN 1 ELSE 0 END
        - CASE WHEN DATEPART(WEEKDAY, EndDate) IN (6,7) THEN 1 ELSE 0 END
        + CASE WHEN DATEPART(WEEKDAY, EndDate) IN (6,7) THEN 0 ELSE 1 END AS FullWorkDays,
        -- 计算开始日期的有效工时
        CASE 
            WHEN DATEPART(WEEKDAY, StartDate) IN (6,7) THEN 0
            ELSE DATEDIFF(MINUTE, 
                CASE WHEN StartDate < CAST(CAST(StartDate AS DATE) AS DATETIME) + '08:00:00' THEN CAST(CAST(StartDate AS DATE) AS DATETIME) + '08:00:00' ELSE StartDate END,
                CAST(CAST(StartDate AS DATE) AS DATETIME) + '16:00:00'
            ) / 60.0
        END AS StartDayHours,
        -- 计算结束日期的有效工时
        CASE 
            WHEN DATEPART(WEEKDAY, EndDate) IN (6,7) THEN 0
            ELSE DATEDIFF(MINUTE, 
                CAST(CAST(EndDate AS DATE) AS DATETIME) + '08:00:00',
                CASE WHEN EndDate > CAST(CAST(EndDate AS DATE) AS DATETIME) + '16:00:00' THEN CAST(CAST(EndDate AS DATE) AS DATETIME) + '16:00:00' ELSE EndDate END
            ) / 60.0
        END AS EndDayHours,
        -- 判断开始和结束是否为同一天
        CASE WHEN CAST(StartDate AS DATE) = CAST(EndDate AS DATE) THEN 1 ELSE 0 END AS IsSameDay
    FROM RequestDates
)
SELECT
    Id,
    -- 计算总有效工时
    CASE 
        WHEN IsSameDay = 1 THEN 
            CASE 
                WHEN DATEPART(WEEKDAY, StartDate) IN (6,7) THEN 0
                ELSE DATEDIFF(MINUTE,
                    CASE WHEN StartDate < CAST(CAST(StartDate AS DATE) AS DATETIME) + '08:00:00' THEN CAST(CAST(StartDate AS DATE) AS DATETIME) + '08:00:00' ELSE StartDate END,
                    CASE WHEN EndDate > CAST(CAST(EndDate AS DATE) AS DATETIME) + '16:00:00' THEN CAST(CAST(EndDate AS DATE) AS DATETIME) + '16:00:00' ELSE EndDate END
                ) / 60.0
            END
        ELSE (FullWorkDays * 8.0) + StartDayHours + EndDayHours
    END AS EffectiveWorkHours
FROM WorkDayCalculations

代码解释 #

1. RequestDates CTE：统一处理请求的起止时间，将未完成请求的结束时间替换为当前时间。
2. WorkDayCalculations CTE：
   • FullWorkDays：通过总天数减去区间内的周末数量，得到完整工作日的数量。
   • StartDayHours：计算开始日期当天的有效工时，若开始时间早于8点则从8点起算，晚于16点则当天工时为0。
   • EndDayHours：计算结束日期当天的有效工时，若结束时间晚于16点则到16点截止，早于8点则当天工时为0。
   • IsSameDay：标记起止日期是否为同一天，避免重复计算首尾工时。
3. 最终查询：根据是否为同一天，分别计算总有效工时，确保结果准确。

改进建议 #

• 若需频繁计算此类工时，建议创建日历表，预先标记每个日期是否为工作日，后续计算可直接关联该表，逻辑更简洁，也便于扩展节假日的排除规则。
• 注意DATEPART(WEEKDAY)的返回值依赖SQL Server的SET DATEFIRST设置，若你的环境中周日为一周第一天（DATEFIRST=7），则周末对应的数值为7和1，需调整代码中的判断条件。

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