跨日期缺勤时长计算问题：修正SQL查询匹配工作日统计规则

问题描述 #

当前SQL查询计算缺勤工作时长时，在跨多天且结束日期为部分工作日的场景下结果错误。例如：

• 缺勤开始：13/04/2024 08:30
• 缺勤结束：15/04/2024 10:45
  原查询返回3个工作日，而实际预期为2.268天（按每日8小时折算）。

工作时间规则：每日08:30-16:30（共8小时），仅统计16:30之前的缺勤时长，不足1天按日占比计算。

预期结果样本：

原查询问题分析 #

原查询的核心问题：

1. 跨天缺勤时直接按总天数-周末数计算，未考虑首尾两天的部分缺勤时长
2. 未结合日期lookup表校验实际工作日（比如排除假期等非工作日期）

修正后的SQL查询 #

假设存在日期lookup表DimDate，包含字段：

• DateKey：日期（date类型）
• IsWorkingDay：是否为工作日（bit类型，1=工作日，0=非工作日）

修正后的查询通过逐日期计算有效缺勤时长，确保结果准确：

SELECT
    A.dteStartDateTime,
    A.dteEndDateTime,
    -- 总时长转换为日占比（每日8小时=480分钟）
    SUM(
        DATEDIFF(MINUTE, 
            -- 当天缺勤开始时间：取工作开始时间和实际开始时间的较晚值
            CASE 
                WHEN CAST(A.dteStartDateTime AS date) = DD.DateKey THEN 
                    IIF(CAST(A.dteStartDateTime AS time) < '08:30', 
                        CAST(DD.DateKey AS datetime) + CAST('08:30' AS datetime), 
                        A.dteStartDateTime)
                ELSE CAST(DD.DateKey AS datetime) + CAST('08:30' AS datetime)
            END,
            -- 当天缺勤结束时间：取工作结束时间和实际结束时间的较早值
            CASE 
                WHEN CAST(A.dteEndDateTime AS date) = DD.DateKey THEN 
                    IIF(CAST(A.dteEndDateTime AS time) > '16:30', 
                        CAST(DD.DateKey AS datetime) + CAST('16:30' AS datetime), 
                        A.dteEndDateTime)
                ELSE CAST(DD.DateKey AS datetime) + CAST('16:30' AS datetime)
            END
        )
    ) / 480.0 AS [Working Time Lost]
FROM TblCoverManagerAbsences A
JOIN TblCoverManagerAbsencesReasons AR 
    ON A.intReason = AR.TblCoverManagerAbsencesReasonsId
OUTER APPLY (
    SELECT Min(txtStartDate) AS StartDate
    FROM TblSchoolManagementTermDates
    WHERE intSchoolYear = CASE 
                              WHEN MONTH(GETDATE()) BETWEEN 9 AND 12 THEN YEAR(GETDATE()) 
                              ELSE YEAR(GETDATE()) - 1 
                          END
) AS StartDate
-- 关联日期lookup表，获取缺勤期间的所有工作日
JOIN DimDate DD
    ON DD.DateKey BETWEEN CAST(A.dteStartDateTime AS date) AND CAST(A.dteEndDateTime AS date)
    AND DD.IsWorkingDay = 1 -- 只统计工作日
WHERE
    AR.txtName = 'Illness' 
    AND CONVERT(date, A.dteStartDateTime) >= CONVERT(date, StartDate.StartDate)
    AND CONVERT(date, A.dteStartDateTime) <= DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()) - 1, 6)
GROUP BY A.dteStartDateTime, A.dteEndDateTime

逻辑说明 #

1. 关联日期lookup表：生成缺勤期间的所有工作日，自动排除周末和假期
2. 逐日期计算时长：
   • 对于缺勤开始当天：取08:30和实际开始时间的较晚值作为有效开始时间
   • 对于缺勤结束当天：取16:30和实际结束时间的较早值作为有效结束时间
   • 中间完整工作日：直接按8小时（480分钟）计算
3. 总时长折算：将所有日期的有效分钟数求和，除以480分钟/天，得到最终的日占比时长

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