Обновление EndDate в таблице DimEmployee

Я работаю над сценарием для создания таблицы DimEmployee Type2. Я хочу, чтобы он создавал новую запись каждый раз, когда происходит изменение сведений о сотрудниках.

Я считаю, что эта часть работает правильно. То, на чем я застрял, правильно обновляет EndDate для записей, которые изменились.

Это работает, если записи не изменились, EndDate равен NULL он также работает для записей, в одном месяце это было так, а в следующем месяце было так (он вставляет правильное значение EndDate).

Однако это не работает, когда в Staging_Employees было несколько идентичных записей, а затем произошли изменения. Он не использует правильное значение EndDate.

P.S. Поле даты в промежуточной таблице действует как дата моментального снимка.

Кто-нибудь может мне с этим помочь?

Спасибо

Например:

Как видите, EndDate для первой записи в таблице DimEmployee теперь 28/02/2023, дата последней идентичной записи перед появлением другой записи для StaffNo 4078. Для второй записи EndDate остается NULL, поскольку есть больше нет записей для StaffNo 4078 после него.

Используемый код:

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
    )
);