SQL - implement SCD type 2 on historical data?

Question

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

LAG or LEAD window functions.

alexherm
– alexherm

2021-08-12 16:57:44 +00:00
Commented Aug 12, 2021 at 16:57 — alexherm
– alexherm, Commented Aug 12, 2021 at 16:57

eshirvana · Accepted Answer · 2021-08-12 17:01:47Z

1

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

answered Aug 12, 2021 at 17:01

eshirvana

24.7k3 gold badges28 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jess8766 Over a year ago

Thank you, just read up on LEAD (havent used this before) and this is really helpful!

Collectives™ on Stack Overflow

SQL - implement SCD type 2 on historical data?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related