SQL Server 基于缺勤记录起止日期拆分行并合理分配损失班次字段

完全可以完成两项核心处理需求：一是将跨周期的单条缺勤记录，拆分为覆盖周期内每个自然日的独立行；二是拆分累计损失班次字段，避免原值全量重复复制到每一行导致的统计冗余。

实现逻辑为构建连续日期序列，与原缺勤表做匹配，当序列日期落在原记录的Start Date到End Date闭区间内时，生成对应单天的独立记录。

• 兼容所有SQL Server版本的递归CTE实现（无版本依赖）：

-- 源数据替换为实际的员工缺勤表
WITH AbsenceRaw AS (
    SELECT
        PersonRef,
        StartDate,
        EndDate,
        ShiftsLost
    FROM dbo.EmployeeAbsenceRecords
),
DateSeq AS (
    -- 递归从每个缺勤记录的开始日期生成连续日期，直到匹配到结束日期
    SELECT
        PersonRef,
        StartDate AS SingleDay,
        EndDate,
        ShiftsLost
    FROM AbsenceRaw
    UNION ALL
    SELECT
        PersonRef,
        DATEADD(DAY, 1, SingleDay) AS SingleDay,
        EndDate,
        ShiftsLost
    FROM DateSeq
    WHERE SingleDay < EndDate
)
SELECT
    PersonRef,
    SingleDay AS StartDate,
    SingleDay AS EndDate,
    ShiftsLost
FROM DateSeq
-- 取消递归层级限制，避免长周期缺勤触发默认100层的递归上限报错
OPTION (MAXRECURSION 0)

• SQL Server 2022及以上版本的高效实现（无递归性能损耗）：

SELECT
    r.PersonRef,
    DATEADD(DAY, gs.step, r.StartDate) AS StartDate,
    DATEADD(DAY, gs.step, r.StartDate) AS EndDate,
    r.ShiftsLost
FROM dbo.EmployeeAbsenceRecords r
-- 按缺勤周期的天数生成从0开始的连续步长
CROSS APPLY GENERATE_SERIES(0, DATEDIFF(DAY, r.StartDate, r.EndDate)) gs

你当前遇到的重复赋值问题，本质是拆分时直接将原行的累计损失值透传到了每一行，没有做拆分计算，会导致后续统计时同一个累计值被重复累加N次，结果严重虚高。
针对你示例中“累计3个损失班次，优先分配给周期内靠前的3个自然日，剩余日期记0”的规则，可在拆分日期的基础上增加排序逻辑，实现精准分配：

WITH AbsenceRaw AS (
    SELECT
        PersonRef,
        StartDate,
        EndDate,
        ShiftsLost
    FROM dbo.EmployeeAbsenceRecords
),
DateSeq AS (
    SELECT
        PersonRef,
        StartDate AS SingleDay,
        EndDate,
        ShiftsLost
    FROM AbsenceRaw
    UNION ALL
    SELECT
        PersonRef,
        DATEADD(DAY, 1, SingleDay) AS SingleDay,
        EndDate,
        ShiftsLost
    FROM DateSeq
    WHERE SingleDay < EndDate
),
DaysRanked AS (
    -- 同一缺勤周期内，按日期从早到晚排序
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY PersonRef, StartDate, EndDate
            ORDER BY SingleDay ASC
        ) AS DayOrderInAbsence
    FROM DateSeq
)
SELECT
    PersonRef,
    SingleDay AS StartDate,
    SingleDay AS EndDate,
    -- 核心分配逻辑：当日在周期内的排序小于等于总损失数，分配1个班次，否则记0
    CASE
        WHEN DayOrderInAbsence <= ShiftsLost THEN 1
        ELSE 0
    END AS ShiftsLost
FROM DaysRanked
OPTION (MAXRECURSION 0)

实际生产场景提示：如果企业有固定排班规则，不要硬编码按日期先后分配，建议拆分完成后关联员工排班表，按照当日实际排班的班次数值对应赋值损失班次，只要保证所有拆分日的损失值总和等于原记录的累计值即可，这种方式更贴合实际业务逻辑。如果存在单日多班的情况，只需将CASE判断中的固定赋值1替换为当日实际排班数，无需改动整体拆分框架。

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