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
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