按年度拆分员工薪酬时间跨度并生成年度起止日期技术求助

我来帮你搞定员工年度薪酬分段记录的问题——核心就是补全那些缺失的年度中间行，同时处理薪酬变动后的薪资折算。下面是一步步的解决方案：

问题梳理 #

我们的目标是给每个员工生成按年度划分的薪酬记录：

• 无论年内是否有薪酬变动，每年至少一条记录
• 如果年内有薪酬调整，要拆分成对应多段年度内记录，并且按实际任职天数折算薪资
• 当前已经用LEAD()函数拿到了薪酬生效的起止时间，但缺少年度与年度之间的完整行（比如John 2011-2019年的记录，2011年只有12/14-12/31的片段，后续年份需要匹配对应薪资并生成完整/分段记录）

先确认基础表结构与测试数据 #

首先把原数据里的表结构补全（注意原EMPLOYEE_CHANGES的CREATE语句漏了EMP_STATUS字段，INSERT里有这个值，所以补上）：

CREATE TABLE [dbo].[EMPLOYEE] ( 
    [EMPID] [VARCHAR](15) NULL, 
    [NAME] [VARCHAR](15) NULL, 
    [FIRST_HIRE_DATE] [DATETIME] NULL, 
    [HIRE_DT] [DATETIME] NULL, 
    [TERM_DT] [DATETIME] NULL 
) ON [PRIMARY]

INSERT INTO [dbo].[EMPLOYEE] VALUES 
('100123', 'John', '2015-12-14 00:00:00.000', '2015-12-14 00:00:00.000', NULL), 
('100124', 'Jane', '2015-02-09 00:00:00.000', '2015-02-09 00:00:00.000', '2019-11-01 00:00:00.000');

CREATE TABLE [dbo].[EMPLOYEE_CHANGES] ( 
    [EMPID] [VARCHAR](15) NULL, 
    [NAME] [VARCHAR](15) NULL, 
    [SALARY_EFFECTIVE_DATE] [DATETIME] NULL, 
    [CHANGE_REASON] [VARCHAR](25) NULL, 
    [EMP_STATUS] [VARCHAR](10) NULL,
    [ANNUAL_SALARY] [money] NULL 
) ON [PRIMARY]

INSERT INTO [dbo].[EMPLOYEE_CHANGES] VALUES 
('100123', 'John', '2011-12-14 00:00:00.000', 'NewHire', 'Active', 100000.00), 
('100123', 'John', '2017-01-01 00:00:00.000', 'AnnualIncrease', 'Active', 110000.00), 
('100124', 'Jane', '2015-02-09 00:00:00.000', 'NewHire', 'Active', 200000.00), 
('100124', 'Jane', '2016-02-13 00:00:00.000', 'AnnualIncrease', 'Active', 215000.00), 
('100124', 'Jane', '2017-02-11 00:00:00.000', 'AnnualIncrease', 'Active', 225000.00), 
('100124', 'Jane', '2019-02-09 00:00:00.000', 'Resignation', 'Terminated', 225000.00)

你现有的查询已经拿到了基础的薪酬生效区间，我们可以基于这个逻辑继续扩展：

SELECT 
    e.empid, 
    e.name, 
    e.FIRST_HIRE_DATE, 
    e.HIRE_DT, 
    e.TERM_DT, 
    NULL AS YEAR, 
    c.ANNUAL_SALARY, 
    c.SALARY_EFFECTIVE_DATE AS SalaryEffStartDt, 
    LEAD(DATEADD(DAY, -1, c.SALARY_EFFECTIVE_DATE), 1, e.TERM_DT) OVER (PARTITION BY c.empid ORDER BY c.SALARY_EFFECTIVE_DATE) AS SalaryEffEndDt 
INTO #test 
FROM employee e 
JOIN EMPLOYEE_CHANGES c ON c.empid = e.EMPID 
ORDER BY empid, SalaryEffStartDt

完整解决方案：生成年度拆分记录并折算薪资 #

核心思路是：

1. 为每个员工生成他们在职期间的所有年度列表
2. 把每个薪酬生效时间段和年度范围做交叉匹配，拆分出年度内的有效时间段
3. 根据年度内的实际任职天数，按比例折算薪资

直接上完整代码：

-- 1. 用递归CTE生成每个员工的在职年度范围
WITH YearRange AS (
    SELECT DISTINCT 
        empid,
        -- 取员工最早的薪酬生效年份作为起始年
        YEAR(MIN(c.SALARY_EFFECTIVE_DATE) OVER (PARTITION BY empid)) AS StartYear,
        -- 取离职年份或当前年份作为结束年
        YEAR(COALESCE(e.TERM_DT, GETDATE())) AS EndYear
    FROM EMPLOYEE e
    JOIN EMPLOYEE_CHANGES c ON e.EMPID = c.empid
),
Years AS (
    -- 递归生成每个员工的年度序列
    SELECT empid, StartYear AS [Year]
    FROM YearRange
    UNION ALL
    SELECT empid, [Year] + 1
    FROM Years
    JOIN YearRange ON Years.empid = YearRange.empid
    WHERE [Year] < YearRange.EndYear
),
-- 2. 获取基础薪酬生效区间（可以直接用你的#test表，这里直接从原表查询更灵活）
SalaryPeriods AS (
    SELECT 
        e.empid, 
        e.name, 
        e.FIRST_HIRE_DATE, 
        e.HIRE_DT, 
        e.TERM_DT, 
        c.ANNUAL_SALARY, 
        c.SALARY_EFFECTIVE_DATE AS SalaryEffStartDt, 
        LEAD(DATEADD(DAY, -1, c.SALARY_EFFECTIVE_DATE), 1, e.TERM_DT) AS SalaryEffEndDt
    FROM employee e 
    JOIN EMPLOYEE_CHANGES c ON c.empid = e.EMPID 
),
-- 3. 交叉匹配年度与薪酬区间，拆分出年度内的有效记录
AnnualSalaryRecords AS (
    SELECT 
        sp.empid,
        sp.name,
        y.[Year],
        sp.ANNUAL_SALARY,
        -- 计算年度内的实际起始日：取薪酬生效开始和年度第一天的最大值
        CASE 
            WHEN sp.SalaryEffStartDt > DATEFROMPARTS(y.[Year], 12, 31) THEN NULL
            ELSE IIF(sp.SalaryEffStartDt > DATEFROMPARTS(y.[Year], 1, 1), sp.SalaryEffStartDt, DATEFROMPARTS(y.[Year], 1, 1))
        END AS AnnualStartDt,
        -- 计算年度内的实际结束日：取薪酬生效结束和年度最后一天的最小值
        CASE 
            WHEN sp.SalaryEffEndDt < DATEFROMPARTS(y.[Year], 1, 1) THEN NULL
            ELSE IIF(COALESCE(sp.SalaryEffEndDt, GETDATE()) < DATEFROMPARTS(y.[Year], 12, 31), COALESCE(sp.SalaryEffEndDt, GETDATE()), DATEFROMPARTS(y.[Year], 12, 31))
        END AS AnnualEndDt
    FROM SalaryPeriods sp
    JOIN Years y ON sp.empid = y.empid
    -- 筛选出有重叠的时间段
    WHERE sp.SalaryEffStartDt <= COALESCE(sp.SalaryEffEndDt, GETDATE())
      AND y.[Year] BETWEEN YEAR(sp.SalaryEffStartDt) AND YEAR(COALESCE(sp.SalaryEffEndDt, GETDATE()))
)
-- 4. 计算折算薪资并输出最终结果
SELECT 
    empid,
    name,
    [Year],
    ANNUAL_SALARY,
    AnnualStartDt,
    AnnualEndDt,
    -- 按年度内实际天数占全年天数的比例折算薪资（考虑闰年）
    CASE 
        WHEN AnnualStartDt IS NULL OR AnnualEndDt IS NULL THEN 0
        ELSE ANNUAL_SALARY * (DATEDIFF(DAY, AnnualStartDt, AnnualEndDt) + 1) / 
             CASE WHEN [Year] % 400 = 0 OR ([Year] % 4 = 0 AND [Year] % 100 != 0) THEN 366 ELSE 365 END
    END AS ProRatedSalary
FROM AnnualSalaryRecords
WHERE AnnualStartDt IS NOT NULL AND AnnualEndDt IS NOT NULL
ORDER BY empid, [Year], AnnualStartDt

关键细节说明 #

• 递归年度生成：确保每个员工从入职到离职/当前的每一年都有对应的记录
• 时间段拆分逻辑：通过比较薪酬生效区间和年度区间，精准拆分出每个年度内的有效薪酬段
• 薪资折算：考虑闰年的情况，按实际任职天数占全年天数的比例计算折算薪资，结果更准确
• 离职员工处理：用COALESCE处理未离职（TERM_DT为NULL）的员工，默认截止到当前日期

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