使用T-SQL获取员工每条记录的最近入职日期

现有T-SQL场景下，存在EMP员工表，字段定义如下：

• Id：员工唯一标识ID
• SD：记录生效开始日期
• ED：记录生效结束日期
• Status：雇佣状态，A代表在职，T代表离职

需求说明：
需要为查询结果新增两个字段：

1. OriginalHireDate：每个员工（按Id分组）对应Status='A'的最早SD值，即员工的原始入职日期
2. RecentHireDate：每条记录对应的最近入职日期，规则为：取该记录之前最后一条Status='T'记录之后的最早SD；如果不存在这样的离职记录，则使用原始入职日期

已实现OriginalHireDate的SQL语句如下：

SELECT Id, SD, ED, Status, Assignment, OriginalHireDate
FROM EMP t1
INNER JOIN (
    SELECT Id, MIN(SD) AS OriginalHireDate
    FROM EMP
    WHERE Status = 'A'
    GROUP BY Id
) t2 ON t1.Id = t2.Id

期望输出结果如下：

实现RecentHireDate的SQL方案 #

WITH EmpWithCycles AS (
    -- 按员工分组、SD排序，标记每条记录所属的入职周期
    SELECT 
        Id, SD, ED, Status, Assignment,
        OriginalHireDate,
        SUM(CASE WHEN Status = 'T' THEN 1 ELSE 0 END) OVER (PARTITION BY Id ORDER BY SD ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CycleNum
    FROM EMP t1
    INNER JOIN (
        SELECT Id, MIN(SD) AS OriginalHireDate
        FROM EMP
        WHERE Status = 'A'
        GROUP BY Id
    ) t2 ON t1.Id = t2.Id
),
CycleHireDates AS (
    -- 计算每个周期对应的最近入职日期
    SELECT 
        Id,
        CycleNum,
        COALESCE(MIN(CASE WHEN Status = 'A' THEN SD END), OriginalHireDate) AS CycleHireDate
    FROM EmpWithCycles
    GROUP BY Id, CycleNum, OriginalHireDate
)
-- 关联周期入职日期到原记录
SELECT 
    e.Id, e.SD, e.ED, e.Status, e.Assignment, e.OriginalHireDate,
    ch.CycleHireDate AS RecentHireDate
FROM EmpWithCycles e
INNER JOIN CycleHireDates ch 
    ON e.Id = ch.Id AND e.CycleNum = ch.CycleNum
ORDER BY e.Id, e.SD;

逻辑说明 #

1. 周期标记：通过窗口函数SUM(CASE...)，按员工分组、SD排序，统计每条记录之前的离职（T）记录数量，以此作为周期编号——每出现一次离职，后续记录进入新周期。
2. 周期入职日期计算：对每个周期，取其中最早的在职（A）记录SD作为该周期的最近入职日期；若周期内无在职记录（纯离职记录），则沿用原始入职日期。
3. 结果关联：将周期入职日期关联回原记录，得到最终的RecentHireDate字段。

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