如何在SQL中结合雇佣事实表与员工表计算起止日期并更新IsManager标识

我有两张SQL表：存储雇佣事实的Dim.Employment表，以及包含IsManager标识的员工信息表Dim.Employee。需要基于雇佣表计算起止日期，并结合IsManager标识的变更对记录进行拆分更新。

Dim.Employment表结构及数据 #

该表中普通角色变更会生成带唯一键、新起止日期的行，但员工成为经理的变更仅记录在Dim.Employee表中，不会在雇佣表生成新行，而是在员工表中生成带新IsManager标识及对应起止日期的行。

Dim.Employee表结构及数据 #

起止日期计算规则 #

• StartDate默认等于EmploymentStartDate
• 若IsManager标识从0变为1，则将原雇佣条目的EndDate设为对应的ManagerStartDate，并以该ManagerStartDate为起始生成新的雇佣行（可重复使用原EmploymentKey）

期望结果 #

我尝试的查询（未得到预期结果） #

SELECT
    E.EmploymentKey,
    E.EmployeeID,
    (SELECT MAX(i) FROM (VALUES (E.EmploymentStartDate), (EE.ManagerStartDate)) T(i)) AS StartDate,
    (SELECT MAX(i) FROM (VALUES (E.EmploymentEndDate), (EE.ManagerEndDate)) T(i)) AS EndDate,
    EE.IsManager
FROM Dim.Employment E, Dim.Employee EE
WHERE
    EE.ManagerStartDate <= E.EmploymentEndDate
    AND EE.ManagerEndDate >= E.EmploymentStartDate
    AND E.EmployeeID = EE.EmployeeID

解决方案 #

核心思路是先找出每个雇佣记录需要拆分的节点（即IsManager从0变1的时间点），然后通过UNION ALL将拆分后的记录和未拆分的记录合并。

以下是兼容大多数SQL数据库的实现方案：

WITH ManagerTransition AS (
    -- 获取员工从非经理转为经理的第一个时间点
    SELECT 
        EmployeeID,
        MIN(ManagerStartDate) AS FirstManagerStartDate
    FROM Dim.Employee
    WHERE IsManager = 1
    GROUP BY EmployeeID
),
SplitEmployment AS (
    -- 处理需要拆分的雇佣记录：生成拆分前的非经理记录
    SELECT
        E.EmploymentKey,
        E.EmployeeID,
        E.EmploymentStartDate,
        MT.FirstManagerStartDate AS EmploymentEndDate,
        'No' AS IsManager
    FROM Dim.Employment E
    JOIN ManagerTransition MT 
        ON E.EmployeeID = MT.EmployeeID
    WHERE E.EmploymentStartDate < MT.FirstManagerStartDate 
        AND E.EmploymentEndDate > MT.FirstManagerStartDate
    UNION ALL
    -- 生成拆分后的经理记录
    SELECT
        E.EmploymentKey,
        E.EmployeeID,
        MT.FirstManagerStartDate AS EmploymentStartDate,
        E.EmploymentEndDate,
        'Yes' AS IsManager
    FROM Dim.Employment E
    JOIN ManagerTransition MT 
        ON E.EmployeeID = MT.EmployeeID
    WHERE E.EmploymentStartDate < MT.FirstManagerStartDate 
        AND E.EmploymentEndDate > MT.FirstManagerStartDate
    UNION ALL
    -- 处理不需要拆分的雇佣记录：完全在非经理阶段的记录
    SELECT
        E.EmploymentKey,
        E.EmployeeID,
        E.EmploymentStartDate,
        E.EmploymentEndDate,
        'No' AS IsManager
    FROM Dim.Employment E
    LEFT JOIN ManagerTransition MT 
        ON E.EmployeeID = MT.EmployeeID
    WHERE MT.FirstManagerStartDate IS NULL 
        OR E.EmploymentEndDate <= MT.FirstManagerStartDate
    UNION ALL
    -- 处理不需要拆分的雇佣记录：完全在经理阶段的记录
    SELECT
        E.EmploymentKey,
        E.EmployeeID,
        E.EmploymentStartDate,
        E.EmploymentEndDate,
        'Yes' AS IsManager
    FROM Dim.Employment E
    JOIN ManagerTransition MT 
        ON E.EmployeeID = MT.EmployeeID
    WHERE E.EmploymentStartDate >= MT.FirstManagerStartDate
)
SELECT *
FROM SplitEmployment
ORDER BY EmployeeID, EmploymentStartDate;

方案说明 #

1. ManagerTransition CTE：先提取每个员工首次成为经理的时间点，这是拆分雇佣记录的关键节点。
2. SplitEmployment CTE：
   • 第一部分：生成被拆分的雇佣记录中，非经理阶段的子记录，结束日期设为首次经理起始时间。
   • 第二部分：生成被拆分的雇佣记录中，经理阶段的子记录，起始日期设为首次经理起始时间。
   • 第三部分：保留完全处于非经理阶段的原始雇佣记录。
   • 第四部分：保留完全处于经理阶段的原始雇佣记录。
3. 最后通过ORDER BY确保结果按员工和起始日期排序，符合预期输出。

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