SQL: Merge two "history tracking" tables

If you have two columns in a table that “remember” their old values in two different historical tables, as it is the case in AdventureWorks with EmployeePayHistory and EmployeeDepartmentHistory, you may need (or just wonder how) to merge these two tables into only one historical table that will keep track of both columns.

This has been my “problem of the day” and, after some thoughts, I came up with a pattern of solution that I’d like to share with you both to see if you have a better solution and to have the code at hand when I’ll need later. :)

Before using the real table, I made up a simple test case with a Product table with only two fields a couple of variation tables (VariationA and VariationB):

-- --  Sample table Products, only two columns A and B. The current value of the row is (D, 4) -- CREATE TABLE Products (     A CHAR (1),     B INT ) INSERT INTO products (A, B) VALUES ('D', 4)  -- --  Variations for column A -- --      ------------------ --      Year    Old Value --      ------------------ --      2001        A --      2002        B --      2003        C --      2005        D -- CREATE TABLE VariationA (        Position INT,     OldValue VARCHAR (5),     ChangeDate DATETIME )  INSERT VariationA (Position, OldValue, ChangeDate) VALUES (1, 'A', '01/01/2001') INSERT VariationA (Position, OldValue, ChangeDate) VALUES (2, 'B', '01/01/2002') INSERT VariationA (Position, OldValue, ChangeDate) VALUES (3, 'C', '01/01/2003') INSERT VariationA (Position, OldValue, ChangeDate) VALUES (4, 'D', '01/01/2005')  -- --  Variations for column B -- --      ------------------ --      Year    Old Value --      ------------------ --      2000        1 --      2003        2 --      2004        3 -- CREATE TABLE VariationB (        Position INT,     OldValue INT,     ChangeDate DATETIME )  INSERT VariationB (Position, OldValue, ChangeDate) VALUES (1, 1, '01/01/2000') INSERT VariationB (Position, OldValue, ChangeDate) VALUES (2, 2, '01/01/2003') INSERT VariationB (Position, OldValue, ChangeDate) VALUES (3, 3, '01/01/2004') 

 

The query that mixes all these variations into a single table is pretty easy but it took me some time to discover. My solution is to detect what was the value of “B” when a variation in “A” happened and vice versa. As the values stored are “old” values, we know that the value of A at a certain date is the value stored in the first variation for A AFTER that date. The only special case is the current time: if no variation record is found then we know that the value to use is the current value of the record in the product table. The same is obviously true for B. 

I ended up with this pattern query:

-- --  AllEvents contains all the events from both VariationA and VariationB, --  and will detect, for each variation that happened, what was --  the value of the other column at that time, building in this way the --  merged variation list. --  The final SELECT will return the sorted and DISTINCTed result.  --  Then final COALESCE is needed because if a value is NULL it  --  means that it should contain the "current" value of the column -- WITH AllEvents AS (  SELECT          OldValueOfA  = Events.OldValueOfA, -- Old value of A         OldValueOfB  = Events.OldValueOfB, -- Old value of B         ChangeDate   = Events.ChangeDate      -- Date of change  FROM              (SELECT                  OldValueOfA = OldValue,                  OldValueOfB = (SELECT TOP 1 OldValue                                   FROM VariationB V                                 WHERE V.ChangeDate >= VariationA.ChangeDate                                 ORDER BY ChangeDate),                  ChangeDate              FROM VariationA          UNION ALL              SELECT                  OldValueOfA = (SELECT TOP 1 OldValue                                   FROM VariationA V                                 WHERE V.ChangeDate >= VariationB.ChangeDate                                 ORDER BY ChangeDate),                  OldValueOfB = OldValue,                  ChangeDate              FROM VariationB         ) Events     ) SELECT DISTINCT     ChangeDate   = ChangeDate,     OldValueOfA  = COALESCE (OldValueOfA, (SELECT A FROM Products)),     OldValueOfB  = COALESCE (OldValueOfB, (SELECT B FROM Products)) FROM     AllEvents ORDER By ChangeDate

 

Clearly, i could have written some VB code in SSIS to solve the same problem. Nevertheless, having a pattern query at hand is useful because the pattern can be easily adapted to any real world situation with only SQL Management Studio at hand. The following code is the implementation of the pattern for AdventureWorks to merge Department and Payment history for Employees:

WITH AllEvents AS (  SELECT          EmployeeID      = Events.EmployeeID,         OldValueOfRate  = Events.OldValueOfRate,           OldValueOfDep   = Events.OldValueOfDep,            ChangeDate      = Events.ChangeDate          FROM              (SELECT                  EmployeeID      = EmployeeID,                 OldValueOfRate  = Rate,                  OldValueOfDep   = (SELECT TOP 1 DepartmentID                                      FROM HumanResources.EmployeeDepartmentHistory V                                     WHERE V.StartDate >= PayHistory.RateChangeDate                                       AND V.EmployeeID = PayHistory.EmployeeID                                     ORDER BY V.StartDate),                  ChangeDate      = RateChangeDate              FROM HumanResources.EmployeePayHistory PayHistory         UNION ALL              SELECT                  EmployeeID      = EmployeeID,                 OldValueOfRate  = (SELECT TOP 1 Rate                                      FROM HumanResources.EmployeePayHistory V                                     WHERE V.RateChangeDate >= DepHistory.StartDate                                       AND V.EmployeeID = DepHistory.EmployeeID                                     ORDER BY V.RateChangeDate),                  OldValueOfDep   = DepHistory.DepartmentID,                  ChangeDate      = StartDate             FROM HumanResources.EmployeeDepartmentHistory DepHistory         ) Events     ) SELECT DISTINCT     EmployeeID     = EmployeeID,     ChangeDate     = ChangeDate,     OldValueOfDep  = COALESCE (                          OldValueOfDep,  (SELECT DepartmentID                             FROM HumanResources.EmployeeDepartmentHistory DepHistory                           WHERE EndDate IS NULL                              AND DepHistory.EmployeeID = AllEvents.EmployeeID)),     OldValueOfRate = COALESCE (                          OldValueOfRate,  (SELECT TOP 1 Rate                            FROM HumanResources.EmployeePayHistory PayHistory                           ORDER BY RateChangeDate))                      FROM     AllEvents ORDER By EmployeeID, ChangeDate 

 

The code can be easily checked filtering for a single EmployeeID (4 is a good candidate for this check). Please note that – in this case – the “current value” is kept in the history table and so the final COALESCE is a bit more intricated but still pretty easy both to write and to understand.

If you have a better pattern for this kind of situation or any comments on it… I’ll be glad to read your comments.