T-SQL在date和getdate之间生成周的行

Question

我有以下数据示例：

目标日期2022:09-06

Id	入口日期
ID238	2022-09-06
ID238	2022-09
ID238	2022-09-06	2022:09-11
ID636	2022-09-30	2022-09-30
ID636	2022-09-30	2022-10-03
ID636	2022-09-30	2022-10-05

with cal (dt) as 
(
select min(TargetDate) from t
union all  select dateadd(day, 1, dt) from cal where dt <= getdate()
)
select   id
,EntryDate
,coalesce(dt, TargetDate) as TargetDate

from    (
select   *
,max(TargetDate) over(partition by id) as max_TargetDate 
from     t
) t 
left join    cal c 
on           max_TargetDate = TargetDate
and c.dt >= t.max_TargetDate 
and datediff(day, max_TargetDate, dt)%7 = 0

option (maxrecursion 0) 

目标日期2022:09-06

id	入口日期
ID238	2022-09-06
ID238	2022-09
ID238	2022-09-06	2022:09-11
ID238	2022-09-06	2022:09-18
ID238	2022-09-06	2022:09-25
ID238	2022-09-06	2022-10-02
ID238	2022-09-06	2022-10-09
ID238	2022-09-06	2022-10-16
ID636	2022-09-30	2022-09-30
ID636	2022-09-30	2022-10-03
ID636	2022-09-30	2022-10-05
ID636	2022-09-30	2022-10-12

在以下解决方案中，我们利用split_string与replicate相结合来生成新记录。

select   id
,EntryDate
,dateadd(day, 7*(row_number() over(partition by id, TargetDate order by (select null))-1), TargetDate) as TargetDate 
from    (
select   *
,max(TargetDate) over(partition by id) as max_TargetDate 
from     t
) t 
outer apply string_split(case TargetDate when max_TargetDate then replicate(',',datediff(day, TargetDate, getdate())/7) end,',')

目标日期2022:09-06

id	入口日期
ID238	2022-09-06
ID238	2022-09
ID238	2022-09-06	2022:09-11
ID238	2022-09-06	2022:09-18
ID238	2022-09-06	2022:09-25
ID238	2022-09-06	2022-10-02
ID238	2022-09-06	2022-10-09
ID238	2022-09-06	2022-10-16
ID636	2022-09-30	2022-09-30
ID636	2022-09-30	2022-10-03
ID636	2022-09-30	2022-10-05
ID636	2022-09-30	2022-10-12