SQL -在历史数据上实现SCD类型2 ?

Question

我的任务是将系统数据导入我们的DW，并在地址维度上实现SCD。现在SCD类型2向前是相对容易做到的(我使用MERGE语句来做到这一点)，但是有些记录可以追溯到几年前，我真的不知道如何处理。示例如下…

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

我的输出应该如下所示。

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

任何想法?这将只是一个初始加载然后接下来的一切都将使用MERGE语句

来处理

Answer 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