计算员工服务年限：移除历史雇佣记录中的错误终止日期'1900-01-01'

核心思路是：仅保留最新雇佣记录中标记为在职的'1900-01-01'终止日期，其余历史记录里的该错误值全部排除。可以通过窗口函数区分最新记录和历史记录，再结合条件过滤实现。

修改后的SQL代码 #

WITH ranked_employee_jobs AS (
    SELECT 
        PERSON_ID, 
        LAST_HIRE_DT, 
        TERMINATION_DT,
        -- 按雇佣日期倒序排序，最新记录排第1
        ROW_NUMBER() OVER (PARTITION BY PERSON_ID ORDER BY LAST_HIRE_DT DESC) AS row_num
    FROM EMPLOYEE_JOB
    WHERE PERSON_ID = '10000669'
)
SELECT 
    PERSON_ID, 
    LAST_HIRE_DT, 
    TERMINATION_DT,
    CASE 
        WHEN Termination_dt = '1900-01-01' 
            THEN DATEDIFF(DAY, Last_Hire_dt, GETDATE()) / 365.25
        ELSE DATEDIFF(DAY, Last_Hire_dt, Termination_dt) / 365.25
    END AS total_years_of_service
FROM ranked_employee_jobs
-- 筛选条件：要么是最新记录（允许保留在职标记），要么终止日期不是错误值
WHERE (row_num = 1) OR (TERMINATION_DT != '1900-01-01')
ORDER BY LAST_HIRE_DT DESC;

代码说明 #

• CTE部分：用ROW_NUMBER()给每个员工的记录按雇佣时间倒序编号，最新的记录编号为1。
• 过滤条件：
  • row_num = 1：保留最新的雇佣记录，不管它的终止日期是不是'1900-01-01'（也就是保留在职状态的标记）。
  • TERMINATION_DT != '1900-01-01'：历史记录里只保留有真实终止日期的条目，排除错误的'1900-01-01'。
• 计算服务年限的逻辑和你原代码一致，仅调整了数据筛选范围。

如果需要批量处理所有员工，只需去掉WHERE PERSON_ID = '10000669'即可。

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