SQL Server 2014 按考核年度查询员工最新任职岗位的实现问题

核心逻辑说明 #

• 考核年度定义：每年5月1日至次年4月30日，例：2022考核年度对应时间范围为2022-05-01 ~ 2023-04-30
• 先统一处理DATETO空值：将NULL替换为9999-12-31，避免日期判断异常
• 生成覆盖所有业务时间的考核年度维度，和任职表做关联匹配，筛选出任职时间段与考核年度存在重叠的记录
• 用ROW_NUMBER()窗口函数按员工ID、考核年度分区，按任职起始日期倒序排序，取排序第一的记录即为该年度最后生效的岗位

完整实现代码 #

WITH 
-- 递归生成考核年度维度，可根据实际业务的最早/最晚任职时间调整起止范围
AssessYearDim AS (
    SELECT 2010 AS AssessYear, CAST('2010-05-01' AS DATE) AS YearStart, CAST('2011-04-30' AS DATE) AS YearEnd
    UNION ALL
    SELECT AssessYear + 1, DATEADD(YEAR, 1, YearStart), DATEADD(YEAR, 1, YearEnd)
    FROM AssessYearDim
    WHERE AssessYear < 2030
),
-- 清洗任职表空值
EmpPositionClean AS (
    SELECT 
        EMPLOYEEID,
        POSITION,
        DATEFROM,
        ISNULL(DATETO, '9999-12-31') AS DATETO
    FROM 员工任职数据表 -- 替换为实际表名
),
-- 匹配任职记录覆盖的所有考核年度并排序
EmpYearRank AS (
    SELECT 
        e.EMPLOYEEID,
        a.AssessYear,
        e.POSITION,
        ROW_NUMBER() OVER (
            PARTITION BY e.EMPLOYEEID, a.AssessYear 
            ORDER BY e.DATEFROM DESC
        ) AS rn
    FROM EmpPositionClean e
    INNER JOIN AssessYearDim a 
        ON e.DATEFROM <= a.YearEnd 
        AND e.DATETO >= a.YearStart
)
-- 取每个员工每个考核年度的最新岗位
SELECT EMPLOYEEID, AssessYear, POSITION
FROM EmpYearRank
WHERE rn = 1
OPTION (MAXRECURSION 100);

常见错误排查 #

之前返回结果不符合预期，90%概率是ROW_NUMBER()的配置错误：

1. 分区条件必须同时包含EMPLOYEEID和AssessYear，缺少任意一个都会导致排序逻辑错误
2. 排序字段必须用DATEFROM倒序，不要用DATETO倒序，避免未结束的任职（DATETO为NULL）抢占错误的排序优先级
   按照上述逻辑执行，员工1048137的2022考核年度会自动匹配该年度内最后生效的任职，返回正确的Coordinator岗位结果。

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