I have the below data sample:

Background: After the 3rd and final TargetDate for each ID, I need to generate rows for weekly TargetDates up to todays date.

I'd like to take the MAX target date for each Id, then add new rows for each subsequent week between this MAX target date and GETDATE().

Ideal output (bold records are the rows I'd like to add) - I'm aware the original target dates aren't in my query. I'm more focussed on pulling the bold dates

I tried the below recursive CTE, but wasn't quite there.

;WITH cte AS 
(
SELECT [Id], [EntryDate], MAX([Target Day]) as [TargetDate]
FROM [Base_List]
GROUP BY  [Id], [EntryDate]

UNION ALL

SELECT cte.[Id], x.[EntryDate], CAST(DATEADD(week,1,[TargetDate]) as date)
FROM cte

INNER JOIN [Base_List] as x
    ON x.[Id] = cte.[Id]
    AND [TargetDate] < GETDATE()

)

SELECT [Id], [EntryDate], [TargetDate]
FROM cte
OPTION (MAXRECURSION 0)

CodePudding user response：

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) 

Fiddle

CodePudding user response：

In the following solutions we leverage split_string with combination with replicate to generate new records.

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,',')

Fiddle

For SQL in Azure and SQL Server 2022 we have a cleaner solution based on ordinal.

"The enable_ordinal argument and ordinal output column are currently supported in Azure SQL Database, Azure SQL Managed Instance, and Azure Synapse Analytics (serverless SQL pool only). Beginning with SQL Server 2022 (16.x) Preview, the argument and output column are available in SQL Server."

select   id
        ,EntryDate
        ,dateadd(day, 7*(ordinal - 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,',', 1)