Type2 DimEmployee表EndDate更新异常问题求助

我正在编写脚本创建Type2 DimEmployee表，已实现员工信息变更时自动生成新记录，但更新已变更记录的EndDate时遇到以下问题：

• 无变更的记录EndDate为NULL，此情况正常；
• 单月信息变更的记录能正确设置EndDate，此情况正常；
• 但当Staging_Employees表中存在多条相同记录后发生信息变更时，无法为对应记录设置正确的EndDate值。

注：Staging_Employees表中的Date字段为快照日期。

示例说明 #

• 源数据（Staging_Employees）：
  
• 当前DimEmployee结果：
  
• 期望DimEmployee结果：
  

如示例所示，DimEmployee表中第一条记录的EndDate应设为2023/02/28（即StaffNo 4078发生变更前最后一条相同记录的快照日期），第二条记录因后续无更多记录，EndDate保持NULL。

使用的代码 #

BEGIN TRY
    TRUNCATE TABLE DimEmployee;
END TRY
BEGIN CATCH
    CREATE TABLE DimEmployee (
        DimEmployeeID INT IDENTITY(1,1) PRIMARY KEY,
        StructureID INT,
        StaffNo INT NOT NULL,
        EmployeeID INT,
        Position varchar(20),
        JobTitle VARCHAR(100),
        ContractType VARCHAR(50),
        AverageHoursPerWeek DECIMAL(5,2),
        WeeksPeryr DECIMAL(5,2),
        HoursPerWeek DECIMAL(5,2),
        PublicHolidayZone VARCHAR(50),
        FTE DECIMAL(5,2),
        AnalysisGroup VARCHAR(50),
        EffectiveDate DATE,
        EndDate DATE
    );
END CATCH;

-- Insert new records with changes in the specified fields
WITH ChangedRecords AS (
    SELECT
        s.StaffNo,
        RANK() OVER (ORDER BY s.StaffNo) AS EmployeeID,
        s.Position, 
        s.JobTitle,
        s.ContractType,
        s.AverageHoursPerWeek,
        s.WeeksPeryr,
        s.HoursPerWeek,
        s.PublicHolidayZone,
        s.FTE,
        s.AnalysisGroup,
        d.StructureID,
        s.Date,
        LAG(d.StructureID) OVER (PARTITION BY s.StaffNo ORDER BY s.Date) AS PreviousStructureID,
        LAG(s.Position) OVER (PARTITION BY s.StaffNo ORDER BY s.Date) AS PreviousPosition,
        LAG(s.JobTitle) OVER (PARTITION BY s.StaffNo ORDER BY s.Date) AS PreviousJobTitle,
        LAG(s.ContractType) OVER (PARTITION BY s.StaffNo ORDER BY s.Date) AS PreviousContractType,
        LAG(s.AverageHoursPerWeek) OVER (PARTITION BY s.StaffNo ORDER BY s.Date) AS PreviousAverageHoursPerWeek,
        LAG(s.WeeksPeryr) OVER (PARTITION BY s.StaffNo ORDER BY s.Date) AS PreviousWeeksPeryr,
        LAG(s.HoursPerWeek) OVER (PARTITION BY s.StaffNo ORDER BY s.Date) AS PreviousHoursPerWeek,
        LAG(s.PublicHolidayZone) OVER (PARTITION BY s.StaffNo ORDER BY s.Date) AS PreviousPublicHolidayZone,
        LAG(s.FTE) OVER (PARTITION BY s.StaffNo ORDER BY s.Date) AS PreviousFTE,
        LAG(s.AnalysisGroup) OVER (PARTITION BY s.StaffNo ORDER BY s.Date) AS PreviousAnalysisGroup,
        ROW_NUMBER() OVER (PARTITION BY s.StaffNo, YEAR(s.Date), MONTH(s.Date), DAY(s.Date) ORDER BY s.Date) AS RowNum
    FROM Staging_Employees AS s
    JOIN DimStructure AS d ON
        s.Directorate = d.Directorate AND
        s.Service = d.Service AND
        s.Section = d.Section AND
        s.Team = d.Team
)

INSERT INTO DimEmployee (StructureID, StaffNo, EmployeeID, Position, JobTitle, ContractType, AverageHoursPerWeek, WeeksPeryr, HoursPerWeek, PublicHolidayZone, FTE, AnalysisGroup, EffectiveDate, EndDate)
SELECT StructureID, StaffNo, EmployeeID, Position, JobTitle, ContractType, AverageHoursPerWeek, WeeksPeryr, HoursPerWeek, PublicHolidayZone, FTE, AnalysisGroup, Date, NULL
FROM ChangedRecords
WHERE (PreviousStructureID IS NULL OR PreviousStructureID <> StructureID
    OR PreviousPosition <> Position
    OR PreviousJobTitle <> JobTitle
    OR PreviousContractType <> ContractType
    OR PreviousAverageHoursPerWeek <> AverageHoursPerWeek
    OR PreviousWeeksPeryr <> WeeksPeryr
    OR PreviousHoursPerWeek <> HoursPerWeek
    OR PreviousPublicHolidayZone <> PublicHolidayZone
    OR PreviousFTE <> FTE
    OR PreviousAnalysisGroup <> AnalysisGroup);

-- Update EndDate for old records with changes in the specified fields
WITH ChangedRecords AS (
    SELECT
        s.StaffNo,
        RANK() OVER (ORDER BY s.StaffNo) AS EmployeeID,
        s.Position, 
        s.JobTitle,
        s.ContractType,
        s.AverageHoursPerWeek,
        s.WeeksPeryr,
        s.HoursPerWeek,
        s.PublicHolidayZone,
        s.FTE,
        s.AnalysisGroup,
        d.StructureID,
        s.Date,
        LAG(d.StructureID) OVER (PARTITION BY s.StaffNo ORDER BY s.Date) AS PreviousStructureID,
        LAG(s.Position) OVER (PARTITION BY s.StaffNo ORDER BY s.Date) AS PreviousPosition,
        LAG(s.JobTitle) OVER (PARTITION BY s.StaffNo ORDER BY s.Date) AS PreviousJobTitle,
        LAG(s.ContractType) OVER (PARTITION BY s.StaffNo ORDER BY s.Date) AS PreviousContractType,
        LAG(s.AverageHoursPerWeek) OVER (PARTITION BY s.StaffNo ORDER BY s.Date) AS PreviousAverageHoursPerWeek,
        LAG(s.WeeksPeryr) OVER (PARTITION BY s.StaffNo ORDER BY s.Date) AS PreviousWeeksPeryr,
        LAG(s.HoursPerWeek) OVER (PARTITION BY s.StaffNo ORDER BY s.Date) AS PreviousHoursPerWeek,
        LAG(s.PublicHolidayZone) OVER (PARTITION BY s.StaffNo ORDER BY s.Date) AS PreviousPublicHolidayZone,
        LAG(s.FTE) OVER (PARTITION BY s.StaffNo ORDER BY s.Date) AS PreviousFTE,
        LAG(s.AnalysisGroup) OVER (PARTITION BY s.StaffNo ORDER BY s.Date) AS PreviousAnalysisGroup
    FROM Staging_Employees AS s
    JOIN DimStructure AS d ON
        s.Directorate = d.Directorate AND
        s.Service = d.Service AND
        s.Section = d.Section AND
        s.Team = d.Team
)
UPDATE de
SET EndDate = (
    SELECT MAX(se.Date)
    FROM Staging_Employees se
    WHERE de.StaffNo = se.StaffNo
    AND se.Date <= de.EffectiveDate
)
FROM DimEmployee de
WHERE de.EndDate IS NULL
AND EXISTS (
    SELECT 1
    FROM ChangedRecords cr
    WHERE cr.StaffNo = de.StaffNo
    AND cr.EmployeeID = de.EmployeeID
    AND cr.Date > de.EffectiveDate
    AND (
        cr.PreviousStructureID <> cr.StructureID
        OR cr.PreviousPosition <> cr.Position
        OR cr.PreviousJobTitle <> cr.JobTitle
        OR cr.PreviousContractType <> cr.ContractType
        OR cr.PreviousAverageHoursPerWeek <> cr.AverageHoursPerWeek
        OR cr.PreviousWeeksPeryr <> cr.WeeksPeryr
        OR cr.PreviousHoursPerWeek <> cr.HoursPerWeek
        OR cr.PreviousPublicHolidayZone <> cr.PublicHolidayZone
        OR cr.PreviousFTE <> cr.FTE
        OR cr.PreviousAnalysisGroup <> cr.AnalysisGroup
    )
);

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