将SQL中历史化错误的表进行历史化

Question

假设我的表中有以下错误的历史数据:

ID     DATE START      DATE END
1      2020-10-16     2020-12-11
1      2020-11-09     2021-01-02     
1      2020-12-11     2021-01-19
1      2021-01-02     2020-12-11
1      2021-01-19     2050-12-31

我要的是:

ID     DATE START      DATE END
1      2020-10-16     2020-11-09
1      2020-11-09     2020-12-11     
1      2020-12-11     2021-01-02
1      2021-01-02     2021-01-19
1      2021-01-19     2050-12-31

最后一个记录必须以"2050-12-31"结束，每个新的日期开始为前一条记录的日期结束。

假设我有数千条记录，所以我不能只做简单的更新。

非常感谢大家对我的帮助。

Answer 1

试试下面的脚本-

演示

WITH CTE
AS
(
select ROW_NUMBER() OVER(order by DATE_START) rn,
* 
from your_table_name
)

select 
a.id,
a.date_start,
case when b.date_start is null then a.date_end else b.date_start end date_end 
from cte a
left join cte b on a.rn = b.rn-1

Answer 2

似乎一个简单的LEAD()函数就能做到这一点:

INSERT INTO #tmp(ID,[DATE START],[DATE END]) VALUES 
(1,'2020-10-16','2020-12-11'),
(1,'2020-11-09','2021-01-02'),     
(1,'2020-12-11','2021-01-19'),
(1,'2021-01-02','2020-12-11'),
(1,'2021-01-19','2050-12-31')

SELECT ID,[DATE START], 
LEAD([DATE START], 1, '12/31/2050') OVER(ORDER BY ID,[DATE START]) [DATE END]
FROM #tmp

Answer 3

您可以在update中使用lead()作为:

with toupdate as (
select h.*,
lead(date_start, 1, '2050-12-31') over (partition by id order by date_start) as next_date_start
from historic h
) 
update toupdate
set date_end = next_date_start
where date_end <> next_date_start;

这是一个db<>小提琴