英文:
Recursively add dates in Azure Synapse Analytics instead of Max Recursion
问题
在表MonthCalender中，我已添加了两个日期范围，并尝试在范围内插入每月的开始日期和月末日期。
| 月份名称 | 月初日期 | 月末日期 ||---------------------|-----------------------|-----------------------|| 以前的月份 |2010-01-01 00:00:00.000|2023-06-01 08:21:30.813|| 更新数值 |2023-06-01 08:21:30.817|2030-01-01 00:00:00.000|
我想要像这样插入每月的开始日期和月末日期：
| 月份名称 | 月初日期 | 月末日期 ||---------------------|-----------------------|-----------------------|| 以前的月份 |2010-01-01 00:00:00.000|2010-02-01 00:00:00.000|| 以前的月份 |2010-02-01 00:00:00.000|2010-03-01 00:00:00.000|| 以前的月份 |2010-03-01 00:00:00.000|2010-04-01 00:00:00.000|| 以前的月份 |2010-04-01 00:00:00.000|2010-05-01 00:00:00.000|| 以前的月份 |2010-05-01 00:00:00.000|2010-06-01 00:00:00.000|| 以前的月份 |2010-06-01 00:00:00.000|2010-07-01 00:00:00.000|
... 直到 2023-06-01 00:00:00.000
代码中使用了maxrecursion，但在synapse中不受支持。我该如何使用循环在范围内插入数据？
;WITH cSequence AS (SELECT '以前的月份' as 月份名称,Cast('2010-01-01 00:00:00.000' as datetime) AS 开始范围,Cast(DATEADD(m, 1, '2010-01-01 00:00:00.000') as datetime) AS 结束范围UNION ALLSELECT CASE WHEN 结束范围 >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) THEN '更新数值' ELSE '以前的月份' END as 月份名称,结束范围,DATEADD(m, 1, 结束范围)FROM cSequenceWHERE DATEADD(m, 1, 结束范围) < '2030-01-01 00:00:00.000' )insert into #MonthCalendarSELECT * FROM cSequenceOPTION (MAXRECURSION 0);
英文:
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 EndRangeUNION ALLSELECT CASE WHEN EndRange >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) THEN 'UpdateValue' ELSE 'Previous Months' END as MonthName,EndRange,DATEADD(m, 1, EndRange)FROM cSequenceWHERE DATEADD(m, 1, EndRange) < '2030-01-01 00:00:00.000' )insert into #MonthCalendarSELECT * FROM cSequenceOPTION (MAXRECURSION 0);
答案1
得分: 1
在您的情况下，我了解到不支持递归或MAXRECURSION选项的使用，因此您可以使用循环在所需的日期范围内插入数据。
DECLARE @StartRange DATETIME = '2010-01-01 00:00:00.000'DECLARE @EndRange DATETIME = '2023-06-01 00:00:00.000'WHILE @StartRange < @EndRangeBEGINDECLARE @NextMonthStart DATETIME = DATEADD(MONTH, 1, @StartRange)INSERT INTO #MonthCalendar (MonthName, MonthStart, MonthEnd)VALUES ('前几个月', @StartRange, @NextMonthStart)SET @StartRange = @NextMonthStartEND
循环将持续，直到@StartRange的值超过@EndRange的值。在每次迭代中，它将通过将1个月添加到当前的@StartRange值来计算下个月的起始日期。
然后，它将为#MonthCalendar表插入一行，其中包含适当的值，包括MonthName、MonthStart和MonthEnd。最后，它将更新@StartRange的值为计算得到的@NextMonthStart值，以进行下一次迭代。
英文:
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 < @EndRangeBEGINDECLARE @NextMonthStart DATETIME = DATEADD(MONTH, 1, @StartRange)INSERT INTO #MonthCalendar (MonthName, MonthStart, MonthEnd)VALUES ('Previous Months', @StartRange, @NextMonthStart)SET @StartRange = @NextMonthStartEND
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论