生成排除节假日与周末的EndDate和StartPaymentDate的SQL语句

编写简单SELECT语句（禁止使用BEGIN、SET、存储过程等复杂T-SQL），基于StartDate计算排除周末和Holiday表中节假日后的日期，规则如下：

• BufferDate默认值为3
• EndDate = 排除节假日和周末后的第3个有效工作日
• StartPaymentDate = 排除节假日和周末后的第4个有效工作日
• 不可将周末与节假日合并为一张表

已尝试的SQL #

DATEADD(day, 3, StartDate) AS DateAdd

Holiday表结构及数据 #

期望输出示例 #

实现SQL #

使用递归CTE生成日期序列，筛选有效工作日后提取目标日期，语句如下：

WITH DateSequence AS (
    -- 初始行：StartDate，有效工作日计数为0
    SELECT 
        StartDate,
        StartDate AS CurrentDate,
        0 AS WorkDayCount,
        3 AS BufferDate
    FROM YourSourceTable  -- 替换为你的源表名称
    UNION ALL
    -- 递归生成后续日期，更新有效工作日计数
    SELECT 
        ds.StartDate,
        DATEADD(day, 1, ds.CurrentDate) AS CurrentDate,
        CASE
            -- 若当前日期是周末或节假日，计数不变
            WHEN DATEPART(weekday, DATEADD(day, 1, ds.CurrentDate)) IN (1, 7)  -- 周日=1，周六=7，根据DATEFIRST设置调整
                OR EXISTS (SELECT 1 FROM Holiday h WHERE h.Date = DATEADD(day, 1, ds.CurrentDate))
            THEN ds.WorkDayCount
            -- 否则计数+1
            ELSE ds.WorkDayCount + 1
        END AS WorkDayCount,
        ds.BufferDate
    FROM DateSequence ds
    -- 终止条件：计数达到4，只需生成到第4个有效工作日即可
    WHERE ds.WorkDayCount < 4
)
-- 聚合获取目标日期，生成备注
SELECT 
    StartDate,
    MAX(CASE WHEN WorkDayCount = 3 THEN CurrentDate END) AS [EndDate (第3天)],
    MAX(CASE WHEN WorkDayCount = 4 THEN CurrentDate END) AS [StartPaymentDate (第4天)],
    CONCAT(BufferDate, ' (默认值)') AS BufferDate,
    -- 拼接日期说明，与示例格式一致
    STUFF(
        (
            SELECT '<br>' + CAST(ROW_NUMBER() OVER(ORDER BY s.CurrentDate) AS VARCHAR(2)) + '. '
                    + CONVERT(VARCHAR(10), s.CurrentDate, 23) + ' = '
                    + DATENAME(weekday, s.CurrentDate)
                    + CASE
                        WHEN DATEPART(weekday, s.CurrentDate) IN (1, 7) THEN '（周末）不计入'
                        WHEN EXISTS (SELECT 1 FROM Holiday h WHERE h.Date = s.CurrentDate) THEN '（节假日）不计入'
                        ELSE ' = 第' + CAST(s.WorkDayCount AS VARCHAR(2)) + '天'
                        + CASE WHEN s.WorkDayCount = 3 THEN ' = EndDate' WHEN s.WorkDayCount = 4 THEN ' = StartPaymentDate' ELSE '' END
                      END
            FROM DateSequence s
            WHERE s.StartDate = ds.StartDate
            ORDER BY s.CurrentDate
            FOR XML PATH horizontalform 的 Lite全世界义对比哈尔�表LN, TYPE
        ).value('.', 'NVARCHAR(MAX)'),
        1, 4, ''
    ) AS Remarks
FROM DateSequence ds
GROUP BY StartDate, BufferDate
OPTION (MAXRECURSION 0);  -- 允许无限递归，适配较长日期范围

关键说明 #

1. 有效工作日判断：仅当日期既不是周末（通过DATEPART(weekday)判断），也不在Holiday表中（通过EXISTS自动去重同日多节假日），才计入有效工作日。
2. DATEFIRST适配：若你的SQL Server设置周一为每周第一天（DATEFIRST=1），需将周末判断条件改为IN (6,7)。
3. 递归终止：仅生成到有效工作日计数为4的日期，避免不必要的递归开销。
4. 备注生成：通过FOR XML PATH拼接日期序列的说明，与示例格式完全匹配。

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