Can I add an extra step to the iteration?

I have a function that needs to calculate x number of business days (excluding weekends and holidays) before or after a specified date.

The problem is that when the last day falls on a weekend or work day, it doesn't add any extra iteration ...

SELECT afidb.dbo.AddBusinessDays(-21,'11/08/2016') 

      

        
        
        
      

    

The above should return 2016-10-7

, but does return 2016-10-10

. October 10 was a holiday, and 2 days before this weekend.

I think this is because on the last iteration @bdays = 0

it exits and doesn't add an extra step.

Am I wrong on this, or is there another problem?

ALTER FUNCTION [dbo].[Addbusinessdays] (
    @bDays INT -- number of business days to add sub.  
   ,@d DATETIME -- variable to hold the date you want to add or sub days to  
    )
RETURNS DATE
AS
BEGIN
    DECLARE @nDate DATETIME -- the working date  
        ,@addsub INT -- factor for add or sub   

    SET @ndate = @d

    IF @bdays > 0
        SET @addsub = 1
    ELSE
        SET @addsub = - 1

    WHILE @bdays <> 0 -- Keep adding/subtracting a day until @bdays becomes 0  
    BEGIN
        -- incr or decr @ndate  
        SELECT @bdays = CASE 
                WHEN (@@datefirst + Datepart(weekday, @ndate)) % 7 IN ( 0 ,1 ) -- ignore if it is Sat or Sunday   
                    THEN CASE 
                            WHEN @bDays = 0
                                THEN @bdays + 1 * @addsub
                            ELSE @bDays
                            END
                WHEN (
                        SELECT Count(*)
                        FROM dbo.tblholidays
                        WHERE holidaydate = @nDate
                        ) > 0
                    -- ignore if it is in the holiday table  
                    THEN CASE 
                            WHEN @bDays = 0
                                THEN @bdays + 1 * @addsub
                            ELSE @bDays
                            END
                ELSE @bdays - 1 * @addsub -- incr or decr @ndate  
                END

        SET @ndate = Dateadd(day, 1 * @addsub, @ndate)
    END

    RETURN Cast(@nDate AS DATE)
END