更新DimEmployee表中的EndDate

huangapple
go评论93阅读模式
英文:

Updating EndDate in DimEmployee table

问题

以下是您提供的内容的中文翻译：

我一直在编写一个脚本来创建一个Type2 DimEmployee表。我希望每当员工的详细信息发生更改时，它都会创建一个新的记录。

我相信我已经正确地完成了这部分工作。我卡住的地方是如何正确更新已更改记录的EndDate。

对于没有更改的记录，它的EndDate是NULL。
对于一个月是这样，下个月是那样的记录，它也能插入正确的EndDate值。

然而，当Staging_Employees中有多个相同的记录，然后发生更改时，它就不使用正确的EndDate值。

附注：分期表中的日期字段充当快照日期

有谁可以帮助我吗？

谢谢

例如：

更新DimEmployee表中的EndDate

更新DimEmployee表中的EndDate

更新DimEmployee表中的EndDate

如您所见，DimEmployee表中第一条记录的EndDate现在是2023年2月28日，这是StaffNo 4078之前出现不同记录的最后相同记录的日期。对于第二条记录，EndDate保持为NULL，因为它之后没有StaffNo 4078的记录。

使用的代码：

（此处省略了代码部分，如果需要继续翻译，请提供特定的代码段或问题。）

英文:

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:

更新DimEmployee表中的EndDate

更新DimEmployee表中的EndDate

更新DimEmployee表中的EndDate

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:

  1. BEGIN TRY
  2.     TRUNCATE TABLE DimEmployee;
  3. END TRY
  4. BEGIN CATCH
  5.     CREATE TABLE DimEmployee (
  6.         DimEmployeeID INT IDENTITY(1,1) PRIMARY KEY,
  7.         StructureID INT,
  8.         StaffNo INT NOT NULL,
  9.         EmployeeID INT,
  10.         Position varchar(20),
  11.         JobTitle VARCHAR(100),
  12.         ContractType VARCHAR(50),
  13.         AverageHoursPerWeek DECIMAL(5,2),
  14.         WeeksPeryr DECIMAL(5,2),
  15.         HoursPerWeek DECIMAL(5,2),
  16.         PublicHolidayZone VARCHAR(50),
  17.         FTE DECIMAL(5,2),
  18.         AnalysisGroup VARCHAR(50),
  19.         EffectiveDate DATE,
  20.         EndDate DATE
  21.     );
  22. END CATCH;
  24. -- Insert new records with changes in the specified fields
  25. WITH ChangedRecords AS (
  26.     SELECT
  27.         s.StaffNo,
  28.         RANK() OVER (ORDER BY s.StaffNo) AS EmployeeID,
  29.         s.Position, 
  30.         s.JobTitle,
  31.         s.ContractType,
  32.         s.AverageHoursPerWeek,
  33.         s.WeeksPeryr,
  34.         s.HoursPerWeek,
  35.         s.PublicHolidayZone,
  36.         s.FTE,
  37.         s.AnalysisGroup,
  38.         d.StructureID,
  39.         s.Date,
  40.         LAG(d.StructureID) OVER (PARTITION BY s.StaffNo ORDER BY s.Date) AS PreviousStructureID,
  41.         LAG(s.Position) OVER (PARTITION BY s.StaffNo ORDER BY s.Date) AS PreviousPosition,
  42.         LAG(s.JobTitle) OVER (PARTITION BY s.StaffNo ORDER BY s.Date) AS PreviousJobTitle,
  43.         LAG(s.ContractType) OVER (PARTITION BY s.StaffNo ORDER BY s.Date) AS PreviousContractType,
  44.         LAG(s.AverageHoursPerWeek) OVER (PARTITION BY s.StaffNo ORDER BY s.Date) AS PreviousAverageHoursPerWeek,
  45.         LAG(s.WeeksPeryr) OVER (PARTITION BY s.StaffNo ORDER BY s.Date) AS PreviousWeeksPeryr,
  46.         LAG(s.HoursPerWeek) OVER (PARTITION BY s.StaffNo ORDER BY s.Date) AS PreviousHoursPerWeek,
  47.         LAG(s.PublicHolidayZone) OVER (PARTITION BY s.StaffNo ORDER BY s.Date) AS PreviousPublicHolidayZone,
  48.         LAG(s.FTE) OVER (PARTITION BY s.StaffNo ORDER BY s.Date) AS PreviousFTE,
  49.         LAG(s.AnalysisGroup) OVER (PARTITION BY s.StaffNo ORDER BY s.Date) AS PreviousAnalysisGroup,
  50.         ROW_NUMBER() OVER (PARTITION BY s.StaffNo, YEAR(s.Date), MONTH(s.Date), DAY(s.Date) ORDER BY s.Date) AS RowNum
  51.     FROM Staging_Employees AS s
  52.     JOIN DimStructure AS d ON
  53.         s.Directorate = d.Directorate AND
  54.         s.Service = d.Service AND
  55.         s.Section = d.Section AND
  56.         s.Team = d.Team
  57. )
  59. INSERT INTO DimEmployee (StructureID, StaffNo, EmployeeID, Position, JobTitle, ContractType, AverageHoursPerWeek, WeeksPeryr, HoursPerWeek, PublicHolidayZone, FTE, AnalysisGroup, EffectiveDate, EndDate)
  60. SELECT StructureID, StaffNo, EmployeeID, Position, JobTitle, ContractType, AverageHoursPerWeek, WeeksPeryr, HoursPerWeek, PublicHolidayZone, FTE, AnalysisGroup, Date, NULL
  61. FROM ChangedRecords
  62. WHERE (PreviousStructureID IS NULL OR PreviousStructureID <> StructureID
  63.     OR PreviousPosition <> Position
  64.     OR PreviousJobTitle <> JobTitle
  65.     OR PreviousContractType <> ContractType
  66.     OR PreviousAverageHoursPerWeek <> AverageHoursPerWeek
  67.     OR PreviousWeeksPeryr <> WeeksPeryr
  68.     OR PreviousHoursPerWeek <> HoursPerWeek
  69.     OR PreviousPublicHolidayZone <> PublicHolidayZone
  70.     OR PreviousFTE <> FTE
  71.     OR PreviousAnalysisGroup <> AnalysisGroup);
  73. -- Update EndDate for old records with changes in the specified fields
  74. WITH ChangedRecords AS (
  75.     SELECT
  76.         s.StaffNo,
  77.         RANK() OVER (ORDER BY s.StaffNo) AS EmployeeID,
  78.         s.Position, 
  79.         s.JobTitle,
  80.         s.ContractType,
  81.         s.AverageHoursPerWeek,
  82.         s.WeeksPeryr,
  83.         s.HoursPerWeek,
  84.         s.PublicHolidayZone,
  85.         s.FTE,
  86.         s.AnalysisGroup,
  87.         d.StructureID,
  88.         s.Date,
  89.         LAG(d.StructureID) OVER (PARTITION BY s.StaffNo ORDER BY s.Date) AS PreviousStructureID,
  90.         LAG(s.Position) OVER (PARTITION BY s.StaffNo ORDER BY s.Date) AS PreviousPosition,
  91.         LAG(s.JobTitle) OVER (PARTITION BY s.StaffNo ORDER BY s.Date) AS PreviousJobTitle,
  92.         LAG(s.ContractType) OVER (PARTITION BY s.StaffNo ORDER BY s.Date) AS PreviousContractType,
  93.         LAG(s.AverageHoursPerWeek) OVER (PARTITION BY s.StaffNo ORDER BY s.Date) AS PreviousAverageHoursPerWeek,
  94.         LAG(s.WeeksPeryr) OVER (PARTITION BY s.StaffNo ORDER BY s.Date) AS PreviousWeeksPeryr,
  95.         LAG(s.HoursPerWeek) OVER (PARTITION BY s.StaffNo ORDER BY s.Date) AS PreviousHoursPerWeek,
  96.         LAG(s.PublicHolidayZone) OVER (PARTITION BY s.StaffNo ORDER BY s.Date) AS PreviousPublicHolidayZone,
  97.         LAG(s.FTE) OVER (PARTITION BY s.StaffNo ORDER BY s.Date) AS PreviousFTE,
  98.         LAG(s.AnalysisGroup) OVER (PARTITION BY s.StaffNo ORDER BY s.Date) AS PreviousAnalysisGroup
  99.     FROM Staging_Employees AS s
  100.     JOIN DimStructure AS d ON
  101.         s.Directorate = d.Directorate AND
  102.         s.Service = d.Service AND
  103.         s.Section = d.Section AND
  104.         s.Team = d.Team
  105. )
  106. UPDATE de
  107. SET EndDate = (
  108.     SELECT MAX(se.Date)
  109.     FROM Staging_Employees se
  110.     WHERE de.StaffNo = se.StaffNo
  111.     AND se.Date <= de.EffectiveDate
  112. )
  113. FROM DimEmployee de
  114. WHERE de.EndDate IS NULL
  115. AND EXISTS (
  116.     SELECT 1
  117.     FROM ChangedRecords cr
  118.     WHERE cr.StaffNo = de.StaffNo
  119.     AND cr.EmployeeID = de.EmployeeID
  120.     AND cr.Date > de.EffectiveDate
  121.     AND (
  122.         cr.PreviousStructureID <> cr.StructureID
  123.         OR cr.PreviousPosition <> cr.Position
  124.         OR cr.PreviousJobTitle <> cr.JobTitle
  125.         OR cr.PreviousContractType <> cr.ContractType
  126.         OR cr.PreviousAverageHoursPerWeek <> cr.AverageHoursPerWeek
  127.         OR cr.PreviousWeeksPeryr <> cr.WeeksPeryr
  128.         OR cr.PreviousHoursPerWeek <> cr.HoursPerWeek
  129.         OR cr.PreviousPublicHolidayZone <> cr.PublicHolidayZone
  130.         OR cr.PreviousFTE <> cr.FTE
  131.         OR cr.PreviousAnalysisGroup <> cr.AnalysisGroup
  132.     )
  133. );

答案1

得分: 0

根据您提供的内容，涉及更新多次更改的记录的EndDate的问题可能与您用于获取新EndDate的子查询有关，因为它似乎只考虑了小于或等于记录被更新的EffectiveDate的Staging_Employees表中的最大日期。

这个子查询可能没有考虑到在这个范围内可能发生的多次更改。

要解决此问题，您需要修改子查询，以考虑小于或等于EffectiveDate并且大于先前EndDate的记录的最大日期。这将确保您选择了具有多次更改的记录的正确EndDate。

  1. -- 更新具有指定字段更改的旧记录的EndDate
  2. WITH ChangedRecords AS (
  3.     -- 与之前相同
  4. )
  5. UPDATE de
  6. SET EndDate = (
  7.     SELECT MAX(se.Date)
  8.     FROM Staging_Employees se
  9.     WHERE de.StaffNo = se.StaffNo
  10.     AND se.Date <= de.EffectiveDate
  11.     AND se.Date > COALESCE(de.EndDate, '1900-01-01') -- 考虑在先前EndDate之后的日期
  12. )
  13. FROM DimEmployee de
  14. WHERE de.EndDate IS NULL
  15. AND EXISTS (
  16.     SELECT 1
  17.     FROM ChangedRecords cr
  18.     WHERE cr.StaffNo = de.StaffNo
  19.     AND cr.EmployeeID = de.EmployeeID
  20.     AND cr.Date > de.EffectiveDate
  21.     AND (
  22.         cr.PreviousStructureID <> cr.StructureID
  23.         OR cr.PreviousPosition <> cr.Position
  24.         OR cr.PreviousJobTitle <> cr.JobTitle
  25.         OR cr.PreviousContractType <> cr.ContractType
  26.         OR cr.PreviousAverageHoursPerWeek <> cr.AverageHoursPerWeek
  27.         OR cr.PreviousWeeksPeryr <> cr.WeeksPeryr
  28.         OR cr.PreviousHoursPerWeek <> cr.HoursPerWeek
  29.         OR cr.PreviousPublicHolidayZone <> cr.PublicHolidayZone
  30.         OR cr.PreviousFTE <> cr.FTE
  31.         OR cr.PreviousAnalysisGroup <> cr.AnalysisGroup
  32.     )
  33. );

希望这可以帮助您解决问题。

英文:

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.

  1.  -- Update EndDate for old records with changes in the specified fields
  2.     WITH ChangedRecords AS (
  3.         -- Same as before
  4.     )
  5.     UPDATE de
  6.     SET EndDate = (
  7.         SELECT MAX(se.Date)
  8.         FROM Staging_Employees se
  9.         WHERE de.StaffNo = se.StaffNo
  10.         AND se.Date &lt;= de.EffectiveDate
  11.         AND se.Date &gt; COALESCE(de.EndDate, &#39;1900-01-01&#39;) -- Consider dates after the previous EndDate
  12.     )
  13.     FROM DimEmployee de
  14.     WHERE de.EndDate IS NULL
  15.     AND EXISTS (
  16.         SELECT 1
  17.         FROM ChangedRecords cr
  18.         WHERE cr.StaffNo = de.StaffNo
  19.         AND cr.EmployeeID = de.EmployeeID
  20.         AND cr.Date &gt; de.EffectiveDate
  21.         AND (
  22.             cr.PreviousStructureID &lt;&gt; cr.StructureID
  23.             OR cr.PreviousPosition &lt;&gt; cr.Position
  24.             OR cr.PreviousJobTitle &lt;&gt; cr.JobTitle
  25.             OR cr.PreviousContractType &lt;&gt; cr.ContractType
  26.             OR cr.PreviousAverageHoursPerWeek &lt;&gt; cr.AverageHoursPerWeek
  27.             OR cr.PreviousWeeksPeryr &lt;&gt; cr.WeeksPeryr
  28.             OR cr.PreviousHoursPerWeek &lt;&gt; cr.HoursPerWeek
  29.             OR cr.PreviousPublicHolidayZone &lt;&gt; cr.PublicHolidayZone
  30.             OR cr.PreviousFTE &lt;&gt; cr.FTE
  31.             OR cr.PreviousAnalysisGroup &lt;&gt; cr.AnalysisGroup
  32.         )
  33.     );

huangapple
  • 本文由 发表于 2023年5月14日 20:35:41
  • 转载请务必保留本文链接：https://go.coder-hub.com/76247514.html
  • sql
  • sql-server
  • t-sql
  • type-2-dimension
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定