Rewrite this Scalar UDF as a Inline TVF-CodePudding

I am trying to write this scalar 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

as an inline TVF.

I have been trying to use CTEs in the TVF to replace the while loop, but I keep running into myriad issues, so if anyone has any ideas and could help I would be incredibly grateful.

Requirements: Take in a date, d, and an integer, i, as parameters and return a date that is that is i many business days (weekdays) from the date, d, argument passed in.

While I appreciate that there may be better ways to go about this, and would love to read them if they're suggested here, I also really would like to know how to accomplish this using recursive CTE(s) in a Inline TVF as I am more doing this as practice so I can apply this technique to more complicated scalar UDF's I may need to refactor in the future.

CodePudding user response：

Converting to a TVF and eliminating the loops is the absolute right thing to do.

As I commented, consider a Calendar Table ... there are many things that can easily be accomplished.

Here is a TVF which uses an ad-hoc tally table to

CREATE FUNCTION [dbo].[YourFunctionName] (@D date,@I int)
Returns Table
Return (

Select WorkDate = D
      ,WorkDays = RN
 From  (
        Select D
              ,RN = -1 row_number() over( order by D)
         From  (
                Select Top ((@I 1)*2) D=dateadd(DAY,-1 Row_Number() Over (Order By (Select NULL)),@D)
                  From master..spt_values n1, master..spt_values n2
               ) A
         Where datename(WEEKDAY,D) not in ('Saturday','Sunday')
       ) A
 Where RN=@I

)

CodePudding user response：

I've actually got a similar requirement to this in an application at work.

Your scalar function is quite usable for around a few tens of days, however is noticeably un-performant above that.

I can't take credit for the following (as I know it was gleaned from various blogs / forums) - but stripping out some bespoke business logic and fitting to your exsting function, the following function computes the date offset and is (probably) the best performance you're going to get:

create or alter function dbo.DAYSADDNOWK2(@addDate date, @numDays int)
returns datetime
as
begin
  return DateAdd(day,
    case
      when ((DatePart(weekday, @addDate)   1) % 7   (@numDays % 5)) > 6 
    then 2 else 0 end   (@numDays % 5), 
    DateAdd(week, (@numDays / 5), @addDate)
  )
end;