SQL Server中合并员工连续任职日期区间的查询需求

原始查询及结果 #

我在SQL Server中执行以下查询：

select 
    personeel_nummer as employee_number,
    vanaf_datum as from_date,
    tot_datum as until_date
from 
    personeel_kenmerken
where 
    personeel_nummer = 32600

得到的结果：

期望结果 #

需要将相邻的日期区间合并，得到员工的完整任职周期，期望结果如下：

解决方案查询语句 #

可以使用窗口函数识别日期区间断点，再分组合并连续区间：

WITH ranked_dates AS (
    SELECT 
        employee_number,
        from_date,
        until_date,
        -- 标记当前区间是否与上一区间连续
        CASE 
            WHEN DATEADD(DAY, 1, LAG(until_date) OVER (PARTITION BY employee_number ORDER BY from_date)) = from_date 
            THEN 0 
            ELSE 1 
        END AS is_break
    FROM (
        SELECT 
            personeel_nummer AS employee_number,
            vanaf_datum AS from_date,
            tot_datum AS until_date
        FROM personeel_kenmerken
        WHERE personeel_nummer = 32600
    ) AS src
),
grouped_intervals AS (
    SELECT 
        employee_number,
        from_date,
        until_date,
        -- 累计求和生成分组ID，连续区间归属同一组
        SUM(is_break) OVER (PARTITION BY employee_number ORDER BY from_date) AS group_id
    FROM ranked_dates
)
SELECT 
    employee_number,
    MIN(from_date) AS from_date,
    MAX(until_date) AS until_date
FROM grouped_intervals
GROUP BY employee_number, group_id
ORDER BY from_date;

思路说明 #

1. 识别断点：通过LAG()函数获取上一条记录的结束日期，判断当前记录的开始日期是否为上一日期的次日，以此区分连续区间与新区间。
2. 生成分组ID：对断点标记做累计求和，连续的区间会得到相同的分组ID。
3. 合并区间：按员工编号和分组ID聚合，取每组的最早开始日期和最晚结束日期，得到合并后的完整任职周期。

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