Build historical tracking of changes to a row base on information in another table

Question

I have an table consisting of all items within our company - the Item table. Each row represents one item with the newest values for each attribute (e.g. unit price, profit for this item, vendor, description and so forth. ) Ex.:

ItemNo | Description | Unit Price | Profit % |
----------------------------------------------
1      |  MyItem     |   200      |   47     |
2      | MyOtherItem |   300      |   25     |

In another table I have historical changes for the Item-table - the ChangeLog History Table It shows the ItemNo, The column the has been changed, the old and the new value and a DateCreated-column showing when the change happened. Like this:

ItemNo | ColumnName  | OldValue | New Value | DateCreated |
-----------------------------------------------------------
1      |  Unit Price |    50    |   100     | 20170401    |
1      |  Unit Price |    100   |   200     | 20170501    |
2      |  Profit %   |     2    |    25     | 20170603    |
1      |  Profit %   |     99   |    47     | 20170604    |

I want to create a table that looks like the Item table, but where all changes are tracked chronologically based on the Changelog Hisotry Table, with a ValidFrom and ValidTo Date. So the Item-table in the beginning should look like this:

ItemNo | Description | Unit Price | Profit % | ValidFrom  | ValidTo
-------------------------------------------------------------------
1      |  MyItem     |   200      |   47     | 2017-06-04 | 9999-12-31
1      |  MyItem     |   200      |   99     | 2017-05-01 | 2017-06-04
1      |  MyItem     |   100      |   99     | 2017-04-01 | 2017-05-01
1      |  MyItem     |   50       |   99     | 1900-01-01 | 2017-04-01
2      | MyOtherItem |   300      |   25     | 2017-06-03 | 9999-12-31
2      | MyOtherItem |   300      |   2      | 1900-01-01 | 2017-06-03

So the question is how do I create that with T-SQL? I alsp have SSIS at my disposal but I have no idea how to fix this, Been trying to get my head around it for hours now.

Thanks in advance!

Answer 1

Try this, for 2 columns:

WITH hist AS (
  -- sample change log data
  select * 
  from (
    Values
         (1,'Unit Price',50 ,100, cast('20170401' as date))
        ,(1,'Unit Price',100,200,'20170501')
        ,(2,'Profit %', 2 , 25,'20170603')
        ,(1,'Profit %', 99, 47,'20170604') 
    ) t(ItemNo,ColumnName,OldValue,NewValue,DateCreated )
),
items AS(
  -- sample items data
  select * 
  from (
    Values
         (1, 'MyItem',211,4)
        ,(2,'MyOtherItem',311,2)
     ) t(ItemNo,Description,UnitPrice,[Profit %])
),
-- Solution
hist2 AS (
    -- Add the very old values from change log as new starting at 1900-01-01
    SELECT * 
    FROM hist
    UNION ALL
    SELECT TOP(1) WITH TIES ItemNo, ColumnName, null, OldValue, '19000101'
    FROM hist
    ORDER BY row_number() over (partition by ItemNo, ColumnName order by DateCreated)   
),    
intv AS (
    -- Get itervals from augmented change log
    SELECT ItemNo
      , sD = DateCreated
      , eD = coalesce(dateadd(day, -1, lead(DateCreated) over (partition by ItemNo order by DateCreated)),'99991231')
    FROM (
        SELECT DISTINCT ItemNo, DateCreated 
        FROM hist2
    ) t
)
-- Fill intervals with values from augmented change log, using Items values as a last resort
SELECT items.ItemNo, items.Description, Sd, Ed
  , coalesce(p1.UnitPrice, items.UnitPrice)
  , coalesce(p2.[Profit %], items.[Profit %])
FROM intv
OUTER APPLY (
    select top(1) UnitPrice = NewValue
    from hist2 h
    where h.ItemNo = intv.ItemNo and h.ColumnName = 'Unit Price' and DateCreated <= Ed
    order by DateCreated desc
    ) p1
OUTER APPLY (
    select top(1) [Profit %] = NewValue
    from hist2  h
    where h.ItemNo = intv.ItemNo and h.ColumnName = 'Profit %' and DateCreated <= Ed
    order by DateCreated desc
    ) p2 
JOIN items ON items.ItemNo = intv.ItemNo

You may want to instantiate hist2 and add proper indices for better perfomance.