SQL Server如何填补员工雇佣日期区间空白并标记为非活跃

现有员工雇佣记录，每条记录包含员工ID、雇佣起始日期、结束日期及活跃状态（Active=1表示活跃）。不同记录间存在日期空白区间，需要为所有员工填补这些空白区间，新增记录标记为非活跃（Active=0）。

示例原始数据：

处理后目标数据：

目前仅能想到按ID分组并按START_DATE排序，但不清楚后续操作，需要解决思路及SQL实现方案。

核心逻辑是通过窗口函数定位每条记录的下一条记录起始日期，识别空白区间后生成非活跃记录，最后合并原始记录与新增记录。

步骤分解 #

• 排序并获取下一条记录的起始日期：按ID分组、START_DATE升序排序，用LEAD()窗口函数获取当前记录的下一条记录起始日期，记为NEXT_START。
• 识别空白区间：若当前记录的END_DATE非空，且NEXT_START晚于END_DATE + 1天，则存在需要填补的空白。
• 生成非活跃记录：针对空白区间生成新记录，ID与原记录一致，START_DATE为原记录END_DATE + 1天，END_DATE为NEXT_START - 1天，Active=0。
• 合并排序：将原始记录与新增的非活跃记录合并，再按ID和START_DATE排序得到完整记录集。

SQL示例（以MySQL为例） #

-- 1. 给每条记录标记下一条记录的起始日期
WITH ranked_records AS (
    SELECT 
        ID,
        START_DATE,
        END_DATE,
        Active,
        LEAD(START_DATE) OVER (PARTITION BY ID ORDER BY START_DATE) AS NEXT_START
    FROM employee_records
),
-- 2. 生成需要填补的非活跃空白记录
gap_records AS (
    SELECT 
        ID,
        DATE_ADD(END_DATE, INTERVAL 1 DAY) AS START_DATE,
        DATE_SUB(NEXT_START, INTERVAL 1 DAY) AS END_DATE,
        0 AS Active
    FROM ranked_records
    WHERE 
        END_DATE IS NOT NULL 
        AND NEXT_START IS NOT NULL 
        AND DATE_ADD(END_DATE, INTERVAL 1 DAY) < NEXT_START
)
-- 3. 合并原始记录与空白记录并排序
SELECT ID, START_DATE, END_DATE, Active
FROM employee_records
UNION ALL
SELECT ID, START_DATE, END_DATE, Active
FROM gap_records
ORDER BY ID, START_DATE;

注意事项 #

• 日期函数需适配不同数据库：比如PostgreSQL用END_DATE + INTERVAL '1 day'，SQL Server用DATEADD(day, 1, END_DATE)。
• 若原始记录的END_DATE为null（表示当前在职），无需处理后续空白。

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