Recursively add dates in Azure Synapse Analytics instead of Max Recursion

Question

In the table MonthCalender, I have added two date ranges and I am trying to insert monthly start and month end dates within the range.

MonthName	MonthStart	MonthEnd
Previous Months	2010-01-01 00:00:00.000	2023-06-01 08:21:30.813
UpdateValue	2023-06-01 08:21:30.817	2030-01-01 00:00:00.000

I want to insert month start and month end dates like this.

MonthName	MonthStart	MonthEnd
Previous Months	2010-01-01 00:00:00.000	2010-02-01 00:00:00.000
Previous Months	2010-02-01 00:00:00.000	2010-03-01 00:00:00.000
Previous Months	2010-03-01 00:00:00.000	2010-04-01 00:00:00.000
Previous Months	2010-04-01 00:00:00.000	2010-05-01 00:00:00.000
Previous Months	2010-05-01 00:00:00.000	2010-06-01 00:00:00.000
Previous Months	2010-06-01 00:00:00.000	2010-07-01 00:00:00.000

... till 2023-06-01 00:00:00.000

The Code is using maxrecursion but that is not supported in synapse. How do I use a loop to insert data within the range?

;WITH cSequence AS (     
SELECT  'Previous Months' as MonthName, 
  Cast('2010-01-01 00:00:00.000' as datetime) AS StartRange,  
  Cast(DATEADD(m, 1, '2010-01-01 00:00:00.000') as datetime) AS EndRange
UNION ALL     
SELECT  CASE WHEN EndRange >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) THEN 'UpdateValue' ELSE 'Previous Months' END as MonthName, 
  EndRange,                  
  DATEADD(m, 1, EndRange)
FROM cSequence      
WHERE DATEADD(m, 1, EndRange) < '2030-01-01 00:00:00.000' )  
insert into #MonthCalendar 
SELECT * FROM cSequence 
OPTION (MAXRECURSION 0);

https://stackoverflow.com/questions/1393951/what-is-the-best-way-to-create-and-populate-a-numbers-table — Charlieface, Jun 01 '23 at 12:00

score 1 · Answer 1 · answered Jun 01 '23 at 12:31

In your case I understood that the use of recursion or MAXRECURSION option is not supported, so you can use a loop to insert data within the desired date range.

DECLARE @StartRange DATETIME = '2010-01-01 00:00:00.000'
DECLARE @EndRange DATETIME = '2023-06-01 00:00:00.000'

WHILE @StartRange < @EndRange
BEGIN
    DECLARE @NextMonthStart DATETIME = DATEADD(MONTH, 1, @StartRange)
    INSERT INTO #MonthCalendar (MonthName, MonthStart, MonthEnd)
    VALUES ('Previous Months', @StartRange, @NextMonthStart)

    SET @StartRange = @NextMonthStart
END

The loop continues until the @StartRange value exceeds the @EndRange value. In each iteration, it will calculate the next month's start date by adding 1 month to the current @StartRange value.

Then, it inserts a row into the #MonthCalendar table with the appropriate values for MonthName, MonthStart, and MonthEnd. As a final step, it will update the @StartRange value to the calculated @NextMonthStart value to proceed to the next iteration.

Recursively add dates in Azure Synapse Analytics instead of Max Recursion

1 Answers1