I have a table like below:

I want to add a new column based on the date and it should look like this

Let's say that there is a @EndDate = 2022-05-31 (that's where it should stop)

I'm having a hard time trying to figure out how to do it in SSMS. Would appreciate any insights! Thanks :)

CodePudding user response：

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

select    ID
         ,Date
         ,dateadd(month, row_number() over(partition by ID  order by (select null)), Date) as NewCol
from     (
         select   *
         from     t
         outer apply string_split(replicate(',',datediff(month, Date, '2022-05-31')-1),',')
         ) t

Fiddle

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

"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
         ,Date
         ,dateadd(month, ordinal, Date) as NewCol
from     (
         select   *
         from     t
         outer apply string_split(replicate(',',datediff(month, Date, '2022-05-31')-1),',',1)
         ) t

CodePudding user response：

There are many ways to "explode" a row into a set, the simplest in my opinion is a recursive CTE:

DECLARE @endpoint date = '20220531';

DECLARE @prev date = DATEADD(MONTH, -1, @endpoint);

WITH x AS
(
  SELECT ID, date, NewCol = DATEADD(MONTH, 1, date) FROM #d
  UNION ALL
  SELECT ID, date, DATEADD(MONTH, 1, NewCol) FROM x
    WHERE NewCol < @prev
)
SELECT * FROM x
  ORDER BY ID, NewCol;

Working example in this fiddle.

Keep in mind that if you could have > 100 months you'll need to add OPTION (MAXRECURSION) (or just consider using a different solution at scale).