如何用SQL计算缺勤周期中当前周的损失工作日？

问题分析 #

你原SQL的核心问题在于子查询未关联具体缺勤记录，且判断逻辑错误——不是判断当前日期是否在周内，而是要判断缺勤周期与当前周的重叠区间，再计算该重叠部分的天数。

解决方案 #

先定义当前周的起止日期（按你原逻辑，以周日为周起始、周六为周结束），再计算每条缺勤记录与当前周的重叠天数，最终按员工汇总求和：

DECLARE @CurrentWeekStart DATE = DATEADD(dd, -(DATEPART(dw, GETUTCDATE()) - 1), CAST(GETUTCDATE() AS DATE));
DECLARE @CurrentWeekEnd DATE = DATEADD(dd, 7 - DATEPART(dw, GETUTCDATE()), CAST(GETUTCDATE() AS DATE));

SELECT 
    PersonKey,
    SUM(
        CASE 
            WHEN MAX(StartDate, @CurrentWeekStart) <= MIN(EndDate, @CurrentWeekEnd)
            THEN DATEDIFF(dd, MAX(StartDate, @CurrentWeekStart), MIN(EndDate, @CurrentWeekEnd)) + 1
            ELSE 0
        END
    ) AS 'Days Lost This Week'
FROM ODS.MyViewAbsence
WHERE PersonKey = '2036612BSL'
GROUP BY PersonKey;

逻辑说明 #

1. 当前周起止计算：用变量存储当前周的起始（周日）和结束（周六）日期，避免重复计算，提升可读性。
2. 重叠天数计算：
   • 取缺勤开始日期与当前周开始的较大值作为重叠区间的起点
   • 取缺勤结束日期与当前周结束的较小值作为重叠区间的终点
   • 若起点<=终点，计算区间内的天数（DATEDIFF+1是因为包含起止当天）；无重叠则计0
3. 汇总求和：按员工Key分组，累加所有重叠天数，得到该员工当前周的损失工作日。

扩展：统计工作日（排除周末） #

如果需要统计自然日之外的工作日，可修改CASE内的计算逻辑，通过总天数减去周末天数得到工作日数：

SUM(
    CASE 
        WHEN MAX(StartDate, @CurrentWeekStart) <= MIN(EndDate, @CurrentWeekEnd)
        THEN DATEDIFF(dd, MAX(StartDate, @CurrentWeekStart), MIN(EndDate, @CurrentWeekEnd)) 
             - DATEDIFF(wk, MAX(StartDate, @CurrentWeekStart), MIN(EndDate, @CurrentWeekEnd)) * 2
             + 1
        ELSE 0
    END
) AS 'Work Days Lost This Week'

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