在Azure Synapse Analytics中用循环替代递归生成月度日期序列

需求说明 #

需要生成2010年1月至2029年12月的月度起始/结束日期记录，插入到#MonthCalendar表中：

• 当月及之后的记录，MonthName字段值为UpdateValue
• 当月之前的记录，MonthName字段值为Previous Months
• 原递归CTE方案因Azure Synapse不支持OPTION (MAXRECURSION 0)，改用循环实现

循环实现的SQL代码 #

-- 初始化临时表（如果未创建）
IF OBJECT_ID('tempdb..#MonthCalendar') IS NULL
BEGIN
    CREATE TABLE #MonthCalendar (
        MonthName VARCHAR(50),
        MonthStart DATETIME,
        MonthEnd DATETIME
    )
END

-- 初始化变量
DECLARE @StartDate DATETIME = '2010-01-01 00:00:00.000'
DECLARE @EndThreshold DATETIME = '2030-01-01 00:00:00.000'
DECLARE @CurrentMonthStart DATETIME
DECLARE @CurrentMonthEnd DATETIME
DECLARE @CurrentMonthName VARCHAR(50)

-- 初始化当前月份起始日期
SET @CurrentMonthStart = @StartDate

-- 循环生成月度记录
WHILE @CurrentMonthStart < @EndThreshold
BEGIN
    SET @CurrentMonthEnd = DATEADD(MONTH, 1, @CurrentMonthStart)
    
    -- 判断当前月份的名称
    SET @CurrentMonthName = CASE 
        WHEN @CurrentMonthStart >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) 
        THEN 'UpdateValue' 
        ELSE 'Previous Months' 
    END
    
    -- 插入记录到临时表
    INSERT INTO #MonthCalendar (MonthName, MonthStart, MonthEnd)
    VALUES (@CurrentMonthName, @CurrentMonthStart, @CurrentMonthEnd)
    
    -- 更新起始日期为下一个月
    SET @CurrentMonthStart = @CurrentMonthEnd
END

代码说明 #

1. 临时表检查：先判断#MonthCalendar是否存在，不存在则创建，避免重复创建报错
2. 变量初始化：定义起始日期、结束阈值（2030年1月），以及存储当前月份信息的变量
3. 循环逻辑：从起始日期开始，每次生成下一个月的结束日期，判断MonthName后插入记录，直到起始日期达到结束阈值
4. 日期计算：用DATEADD(MONTH, 1, ...)确保每个月的起始/结束日期准确，当月判断逻辑和原CTE保持一致

内容的提问来源于stack exchange，提问作者Siddcity