I have been working on a script to create a Type2 DimEmployee Table. I want it to create a new record every time there is a change to the employees details.
I believe I have this part working correctly. What I am stuck on is correctly updating the EndDate for records that have changed.
It works were records have not changed the EndDate is NULL it also works for records were one month it was this and then the next month it was that (it inserts the correct EndDate value).
However where it is not working is when there has been multiple identical records in the Staging_Employees and then there has been a change. It does not use the correct EndDate value.
P.S the Date feild in the staging table acts as a snapshot date
can anyone help me with this?
Thanks
For example:
As you can see, the EndDate for the first record in the DimEmployee table is now 28/02/2023, the date of the last identical record before a different record appears for StaffNo 4078. For the second record, EndDate remains NULL as there are no more records for StaffNo 4078 after it.
Code Used:
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
)
);
Based on what you provided, the issue with updating the EndDate for records that have changed multiple times may be related to the subquery you are using to get the new EndDate
since it seems that it only considers the maximum date from the Staging_Employees
table that is less than or equal to the EffectiveDate
of the record being updated.
This subquery may not take into account the multiple changes that might have occurred within that range.
To resolve this issue, you need to modify the subquery to consider the maximum date that is both less than or equal to the EffectiveDate
and also greater than the previous EndDate
of the record being updated. This will ensure that you're selecting the correct EndDate
for records that have multiple changes.
-- Update EndDate for old records with changes in the specified fields
WITH ChangedRecords AS (
-- Same as before
)
UPDATE de
SET EndDate = (
SELECT MAX(se.Date)
FROM Staging_Employees se
WHERE de.StaffNo = se.StaffNo
AND se.Date <= de.EffectiveDate
AND se.Date > COALESCE(de.EndDate, '1900-01-01') -- Consider dates after the previous EndDate
)
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
)
);