将Scalar UDF改写为Inline TVF：基于递归CTE实现工作日累加

问题背景 #

原标量UDF实现了"给定起始日期和工作日数，返回之后第N个工作日"的逻辑，现需要将其改写为内联表值函数（Inline TVF），优先使用递归CTE实现以练习该技术，同时也接受其他高效方案。

原标量UDF代码：

CREATE FUNCTION [dbo].[DAYSADDNOWK](@addDate AS DATE, @numDays AS INT)
RETURNS DATETIME
AS
BEGIN
    WHILE @numDays>0
    BEGIN
       SET @addDate=DATEADD(d,1,@addDate)
       IF DATENAME(DW,@addDate)='saturday' SET @addDate=DATEADD(d,1,@addDate)
       IF DATENAME(DW,@addDate)='sunday' SET @addDate=DATEADD(d,1,@addDate)
  
       SET @numDays=@numDays-1
    END
  
    RETURN CAST(@addDate AS DATETIME)
END
GO

递归CTE实现的内联表值函数 #

以下是用递归CTE替代原WHILE循环的Inline TVF实现：

CREATE FUNCTION [dbo].[DAYSADDNOWK_TVF]
(
    @addDate DATE,
    @numDays INT
)
RETURNS TABLE
AS
RETURN
(
    WITH RecursiveDates AS
    (
        -- 基准成员：初始化起始日期和剩余工作日数
        SELECT 
            CurrentDate = @addDate,
            RemainingDays = @numDays
        UNION ALL
        -- 递归成员：逐天推进，自动跳过周末
        SELECT
            CurrentDate = CASE 
                WHEN DATENAME(DW, DATEADD(DAY, 1, rd.CurrentDate)) IN ('Saturday', 'Sunday') 
                THEN DATEADD(DAY, 2, rd.CurrentDate)
                ELSE DATEADD(DAY, 1, rd.CurrentDate)
            END,
            RemainingDays = rd.RemainingDays - 1
        FROM RecursiveDates rd
        WHERE rd.RemainingDays > 0
    )
    -- 提取递归结束时的最终日期
    SELECT CAST(CurrentDate AS DATETIME) AS ResultDate
    FROM RecursiveDates
    WHERE RemainingDays = 0
)
GO

关键说明 #

1. 基准成员：初始化递归的起始状态，包含输入的起始日期和剩余需要添加的工作日数。
2. 递归成员：每次迭代时，判断下一天是否为周末，若是则直接加2天跳过周末，否则加1天；同时剩余工作日数减1，直到剩余天数为0。
3. 递归深度限制：如果@numDays超过100（SQL Server默认递归深度上限），调用时需添加OPTION (MAXRECURSION 0)解除限制，例如：

   SELECT ResultDate FROM dbo.DAYSADDNOWK_TVF('2024-05-20', 150) OPTION (MAXRECURSION 0);
   

高效非递归实现方案 #

如果追求更高性能（尤其是处理大数值@numDays时），可以用数学计算直接推导目标日期，避免循环/递归：

CREATE FUNCTION [dbo].[DAYSADDNOWK_Efficient]
(
    @addDate DATE,
    @numDays INT
)
RETURNS TABLE
AS
RETURN
(
    SELECT 
        CAST(
            DATEADD(DAY, 
                @numDays + 
                -- 计算起始到临时日期之间的周末总数
                (DATEDIFF(WEEK, @addDate, DATEADD(DAY, @numDays, @addDate)) * 2) +
                -- 调整起始日期为周末的情况
                CASE DATENAME(DW, @addDate)
                    WHEN 'Saturday' THEN 2
                    WHEN 'Sunday' THEN 1
                    ELSE 0
                END +
                -- 调整临时结束日期为周末的情况
                CASE DATENAME(DW, DATEADD(DAY, @numDays, @addDate))
                    WHEN 'Saturday' THEN 1
                    WHEN 'Sunday' THEN 2
                    ELSE 0
                END,
                @addDate
            ) AS DATETIME
        ) AS ResultDate
)
GO

逻辑说明 #

通过计算起始日期到"起始日期+N天"之间的周数，得出需要跳过的周末数量，再结合起始/临时结束日期是否为周末的调整值，直接算出最终工作日日期，性能远优于循环或递归方案。

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