自动化员工部门变更：更新旧记录结束日期并插入新记录

背景信息 #

现有两张业务表：

• tblEmployee：从HR系统同步的员工主表，存储员工当前最新信息
• tblEmployeeDeptByDay：自定义的员工部门历史记录表，按日期维度记录员工所属部门的变更

示例测试数据的SQL脚本如下：

DROP TABLE IF EXISTS #tblEmployee
DROP TABLE IF EXISTS #tblEmployeeDeptByDay
-- 创建员工主表测试数据
SELECT '123456' AS ID
        , 'John Smith' AS EmpName
        , 99999 AS DeptID -- 今日刚更新为新部门ID
        , DATEADD(D, DATEDIFF(d, 0, getdate()), 0) AS StartDate
        , 'blah@blah.com' Email
        , 'X4' ShiftPattern
        , 'Tom Jones' AS Manager
        , 'W33321' AS Ext
INTO #tblEmployee
-- 创建员工部门日记录表测试数据
SELECT * 
INTO #tblEmployeeDeptByDay
FROM (
SELECT 
     '123456' AS ID
    , 548758 AS DeptID
    , DATEADD(YY, DATEDIFF(YY,0,GETDATE()), 0) AS StartDate
    , DATEADD(D, DATEDIFF(d, 0, getdate())-1, 0) AS EndDate
UNION ALL
SELECT 
     '123456' AS ID
    , 548758 AS DeptID
    , DATEADD(D, DATEDIFF(d, 0, getdate()), 0) AS StartDate
    , NULL AS EndDate
    ) AS a

-- 查看测试数据
SELECT * FROM #tblEmployee
SELECT * FROM #tblEmployeeDeptByDay

问题描述 #

当HR调整员工部门时，需要完成两个操作：

1. 更新tblEmployeeDeptByDay中旧部门记录的结束日期
2. 插入新部门的生效记录

目前已实现第二个操作，但无法自动完成旧部门记录的结束日期更新，尝试过CTE、LEAD函数等方法均未成功，希望实现示例中tblEmployeeDeptByDay的最终数据状态。

解决方案 #

可以通过关联两张表的简单更新语句实现旧记录的自动收尾，配合插入语句完成全流程自动化：

1. 更新旧部门记录的结束日期 #

UPDATE ed
SET ed.EndDate = DATEADD(DAY, -1, e.StartDate)
FROM #tblEmployeeDeptByDay ed
INNER JOIN #tblEmployee e 
    ON ed.ID = e.ID
WHERE ed.EndDate IS NULL -- 筛选未结束的部门记录
  AND ed.DeptID != e.DeptID; -- 筛选与当前HR系统中部门不一致的旧记录

2. 插入新部门生效记录（补充示例） #

INSERT INTO #tblEmployeeDeptByDay (ID, DeptID, StartDate, EndDate)
SELECT 
    e.ID, 
    e.DeptID, 
    e.StartDate, 
    NULL -- 新部门记录默认未结束
FROM #tblEmployee e
WHERE NOT EXISTS (
    -- 避免重复插入已存在的有效部门记录
    SELECT 1
    FROM #tblEmployeeDeptByDay ed
    WHERE ed.ID = e.ID
      AND ed.DeptID = e.DeptID
      AND ed.EndDate IS NULL
);

逻辑说明 #

• 更新逻辑：找到员工未结束的旧部门记录，将其结束日期设为新部门生效日期的前一天，保证部门记录的时间连续性。
• 插入逻辑：检查日记录表中是否已存在该员工当前部门的未结束记录，不存在则插入，避免重复数据。

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