求Microsoft SQL Server查询员工全年未入职月份区间的SQL语句

场景与示例数据 #

员工可同时任职于多个部门，需找出每个员工未入职的日期区间。以下为示例数据：

预期结果 #

• 员工395961：未入职区间为2022年4月1日至5月31日
• 员工395962：未入职区间为2022年5月1日至5月31日
• 员工395963：无空缺

用户思路与参考代码 #

用户尝试先获取员工有入职记录的月份并去重，再找出缺失月份确定未入职区间，但尚未找到正确实现方式。参考代码如下：

SELECT  DATENAME(MONTH, DATEADD(MONTH, x.number, @Start_Date)) AS MonthName
FROM    master.dbo.spt_values x
WHERE   x.type = 'P'        
AND     x.number <= DATEDIFF(MONTH, @Start_Date, @End_Date)

数据库环境为Microsoft SQL Server 2017，要求避免使用变量以适配大量数据。

解决方案 #

方案1：基于日期覆盖标记的空缺查找 #

通过生成日期序列、标记每个日期是否被入职区间覆盖，再用窗口函数识别空缺区间的起止点，适合需要精确到天的场景：

WITH EmployeeDateRanges AS (
    -- 获取每个员工的时间边界：最早入职日到最晚离职日
    SELECT 
        员工ID,
        MIN(CAST(开始日期 AS DATE)) AS MinStartDate,
        MAX(CAST(结束日期 AS DATE)) AS MaxEndDate
    FROM YourTableName -- 替换为实际表名
    GROUP BY 员工ID
),
DateSeries AS (
    -- 生成覆盖所有员工时间范围的连续日期序列
    SELECT 
        DATEADD(DAY, number, (SELECT MIN(MinStartDate) FROM EmployeeDateRanges)) AS DateValue
    FROM master.dbo.spt_values
    WHERE type = 'P'
        AND DATEADD(DAY, number, (SELECT MIN(MinStartDate) FROM EmployeeDateRanges)) <= (SELECT MAX(MaxEndDate) FROM EmployeeDateRanges)
),
EmployeeDateCoverage AS (
    -- 标记每个员工的每一天是否被入职区间覆盖
    SELECT 
        edr.员工ID,
        ds.DateValue,
        CASE WHEN EXISTS (
            SELECT 1 
            FROM YourTableName t
            WHERE t.员工ID = edr.员工ID
                AND CAST(t.开始日期 AS DATE) <= ds.DateValue
                AND CAST(t.结束日期 AS DATE) >= ds.DateValue
        ) THEN 1 ELSE 0 END AS IsCovered
    FROM EmployeeDateRanges edr
    CROSS JOIN DateSeries ds
    WHERE ds.DateValue BETWEEN edr.MinStartDate AND edr.MaxEndDate
),
GapStarts AS (
    -- 识别空缺区间的起始日期
    SELECT 
        员工ID,
        DateValue AS GapStart
    FROM EmployeeDateCoverage
    WHERE IsCovered = 0
        AND (LAG(IsCovered) OVER (PARTITION BY 员工ID ORDER BY DateValue) = 1 OR LAG(IsCovered) OVER (PARTITION BY 员工ID ORDER BY DateValue) IS NULL)
),
GapEnds AS (
    -- 识别空缺区间的结束日期
    SELECT 
        员工ID,
        DateValue AS GapEnd
    FROM EmployeeDateCoverage
    WHERE IsCovered = 0
        AND (LEAD(IsCovered) OVER (PARTITION BY 员工ID ORDER BY DateValue) = 1 OR LEAD(IsCovered) OVER (PARTITION BY 员工ID ORDER BY DateValue) IS NULL)
)
-- 合并空缺区间的起止点
SELECT 
    gs.员工ID,
    gs.GapStart AS 未入职开始日期,
    ge.GapEnd AS 未入职结束日期
FROM GapStarts gs
JOIN GapEnds ge ON gs.员工ID = ge.员工ID AND gs.GapStart <= ge.GapEnd
GROUP BY gs.员工ID, gs.GapStart, ge.GapEnd
ORDER BY gs.员工ID, gs.GapStart;

方案2：基于合并入职区间的空缺查找 #

先递归合并每个员工的重叠/连续入职区间，再直接查找合并后区间之间的空隙，效率更高，适合数据量较大的场景：

WITH MergedEmployeeRanges AS (
    -- 递归合并员工的重叠或连续入职区间
    SELECT 
        员工ID,
        CAST(开始日期 AS DATE) AS StartDate,
        CAST(结束日期 AS DATE) AS EndDate
    FROM YourTableName -- 替换为实际表名
    UNION ALL
    SELECT 
        mer.员工ID,
        CASE WHEN CAST(t.开始日期 AS DATE) <= mer.EndDate THEN mer.StartDate ELSE CAST(t.开始日期 AS DATE) END,
        CASE WHEN CAST(t.结束日期 AS DATE) >= mer.EndDate THEN CAST(t.结束日期 AS DATE) ELSE mer.EndDate END
    FROM MergedEmployeeRanges mer
    JOIN YourTableName t ON mer.员工ID = t.员工ID
        AND CAST(t.开始日期 AS DATE) <= DATEADD(DAY, 1, mer.EndDate)
        AND CAST(t.结束日期 AS DATE) > mer.EndDate
),
UniqueMergedRanges AS (
    -- 去重合并后的区间
    SELECT DISTINCT 员工ID, StartDate, EndDate
    FROM MergedEmployeeRanges
)
-- 查找合并区间之间的空隙
SELECT 
    u.员工ID,
    DATEADD(DAY, 1, u.EndDate) AS 未入职开始日期,
    LEAD(u.StartDate) OVER (PARTITION BY u.员工ID ORDER BY u.StartDate) AS 未入职结束日期
FROM UniqueMergedRanges u
WHERE LEAD(u.StartDate) OVER (PARTITION BY u.员工ID ORDER BY u.StartDate) > DATEADD(DAY, 1, u.EndDate)
ORDER BY u.员工ID, 未入职开始日期;

方案说明 #

• 两种方案均不使用变量，完全基于表数据动态生成所需范围，适配大量数据场景
• 方案1精确到每一天，适合需要细粒度日期检查的需求；方案2仅处理区间，性能更优
• 使用master.dbo.spt_values生成序列是SQL Server中无需额外创建表的常用方式，若数据范围超过spt_values的number最大值（默认是2047），可改用递归CTE生成更长的日期序列

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