sql - 需要建议以根据审计表重新创建表的历史记录

Question

好的伙计们，我有紧急需要。我的任务是根据每个字段的单独审计表追溯地重新创建事件表的多个字段的准确更改历史记录。我有一个 SQL Server 代理作业，目前从 2012 年 10 月 16 日开始每天执行此操作，但需要立即添加记录以将历史记录改回 2012 年 1 月 1 日。

为了解决我的问题，我需要根据这些审计记录自上而下地反射(reflect)整个层次结构的日常变化。

这是我所拥有的:

事件表:

empId varchar(20),
first_name varchar(50),
last_name varchar(50),
cost_center varchar(20), -- need to re-create history
mgr_empId varchar(20), -- need to re-create history
start_dt date, -- starting date range of active record
end_dt date, -- end of active record - default value of 12/31/2999 = active record
hierarchy_empId varchar (200), --derived field from a nasty self-referencing function (12x)
hierarchy_name varchar(1000) -- derived from the same function as the field above.

例子:

员工 A1000，Tim Lee 于 2012 年 3 月 1 日进入组织，经理 Bill A 的员工 ID B1000 进入成本中心 01552。

A1000  Tim Lee  01552   B1000   3/1/2012   12/31/2999   B1000>A1000   Bill A>Tim LeeB1000  Bill A   01552   NULL    1/1/2011   12/31/2999   B1000         Bill A

On 5/1/2012 the nightly job picks up a delta for Tim, and Tim's cost center changes to 31550. So his current record is updated with an end date of yesterday, and a new "current" record is entered, reflected below:

A1000  Tim Lee  01552   B1000   3/1/2012   4/30/2012   B1000>A1000   Bill A>Tim LeeB1000  Bill A   01552   NULL    1/1/2011   12/31/2999  B1000         Bill AA1000  Tim Lee  31550   B1000   5/1/2012   12/31/2999  B1000>A1000   Bill A>Tim Lee

On 6/15/2012, a new manager Ben C, emp Id C1000, was hired underneath Tim's manager:

A1000  Tim Lee  01552   B1000   3/1/2012    4/30/2012   B1000>A1000         Bill A>Tim LeeB1000  Bill A   01552   NULL    1/1/2011    12/31/2999  B1000               Bill AA1000  Tim Lee  31550   B1000   5/1/2012    6/14/2012   B1000>A1000         Bill A>Tim LeeC1000  Ben C    31550   A1000   6/15/2012   12/31/2999  A1000>C1000         Bill A>Ben CA1000  Tim Lee  31550   B1000   6/15/2012   12/31/2999  B1000>C1000>A1000   Bill A>Ben C>Tim Lee

These updates must trickle down the hierarchy. So, if something changes above Tim, it also must be reflected in his record.

On 7/15, Tim's original manager Bill A gets a new manager, Mark S, emp Id X3000.

A1000  Tim Lee  01552   B1000   3/1/2012    4/30/2012   B1000>A1000               Bill A>Tim LeeB1000  Bill A   01552   NULL    1/1/2011    7/14/2012   B1000                     Bill AA1000  Tim Lee  31550   B1000   5/1/2012    6/14/2012   B1000>A1000               Bill A>Tim LeeC1000  Ben C    31550   A1000   6/15/2012   7/14/2012   A1000>C1000               Bill A>Ben CA1000  Tim Lee  31550   C1000   6/15/2012   7/14/2012   B1000>C1000>A1000         Bill A>Ben C>Tim LeeX3000  Mark S   01000   NULL    7/15/2012   12/31/2999  X3000                     Mark SA1000  Tim Lee  31550   C1000   7/15/2012   12/31/2999  X3000>B1000>C1000>A1000   Mark S>Bill A>Ben C>Tim LeeB1000  Bill A   01552   X3000   7/15/2012   12/31/2999  X3000>B1000               Mark S>Bill AC1000  Ben C    31550   B1000   7/15/2012   12/31/2999  X3000>B1000>C1000              Mark S>Bill A>Ben C

I have these audit tables:

tblManagerAudit

empId varchar(20),
chgTimestamp datetime,
oldMgr varchar(20),
newMgr varchar(20)

tblCostCenterAudit

empId varchar(20),
chgTimnestamp datetime,
oldCostCenter varchar(20),
newCostCenter varchar(20)

很长一段时间以来，我一直在努力解决如何合并审计记录的问题，并感谢任何意见。

Answer 1

我认为这应该让你继续:

SELECT ma1.*, ma2.*
FROM ManagerAudit ma1
LEFT JOIN ManagerAudit ma2
ON ma1.empId = ma2.empId
    and ma1.chgTimestamp < ma2.chgTimestamp
AND NOT EXISTS
  (SELECT * FROM ManagerAudit ma3
   WHERE ma3.empID = ma1.empID
         AND ma3.chgTimestamp > ma1.chgTimestamp
         AND ma3.chgTimestamp < ma2.chgTimestamp)