Search code examples
sqlsql-server-2016

SQL - implement SCD type 2 on historical data?


I have been tasked to import system data into our DW and implement SCD on the address Dimension. Now the SCD type 2 going forward is relatively easy to do (I am using a MERGE statement to do this) however there are records that go back years which I don't really know how to handle. Example as below..

ID           Created          HouseNumber          Address         Postcode
5563        01-03-2016            55                court           m37 7hh
5563        06-08-2020            65                high rd         sk7 7hy
2678        23-04-2017            2                 test            juh shh
2678        11-02-2021            1                 new rd          tes tes

My output should look like the below..

ID      Number          Address         Postcode        From           To             Latest   
5563    55                court           m37 7hh        01-03-2016     06-08-2020      0
5563    65                high rd         sk7 7hy        06-08-2020     31-12-9999      1
2678    2                 test            juh shh        23-04-2017     11-02-2021      0
2678    1                 new rd          tes tes        11-02-2021     31-12-9999      1

Any ideas? this will just be an initial load then everything going forward will be handled using my MERGE statement


Solution

  • select ID, Created, HouseNumber, Address, Postcode,
        Created as FromDate,
        LEAD(Created) over (partition by PK order by Created) as ToDate,
        case when LEAD(Created) over (partition by PK order by Created) is null then 1 else 0 end as Latest
    from factTable