Exclude weekends and public holiday for SQL datediff

this code works but seems like the results are off by 1 day

Objective: Find the datediff for the two dates given.

However, we will have to exclude

• Weekends
• Public Holiday

Example

The difference between 2017-11-04 00:00:00 and 2017-11-22 10:21:00

• Expected Results: 12.42 (Excluded 3 Saturdays & Sundays)

• Current Results: 13.42

The code is not written by me, so i’m trying to understand it. I commented some parts that i am unclear as well.

/**This part is to find the difference in time for the two dates**/
DECLARE @temp AS DECIMAL(10,2) = CAST(DATEDIFF(hour, CONVERT(time, @StartDate), CONVERT(time, @ENDDate)) as Decimal(10,2))/24.00


Declare @numdays int=0

/**This part is to find the difference in date for the two dates**/
/**Issues likely to come from here**/
IF DATEDIFF(day,@StartDate,@ENDDate)>0 
BEGIN
    WHILE DATEDIFF(day,@StartDate,@ENDDate)>0
    BEGIN 
        SET  @StartDate=DATEADD(d,1,@StartDate) 
        WHILE exists (SELECT Holiday_Date FROM Holiday where Holiday_Date=CONVERT(date, @StartDate))  or DATENAME(DW,@StartDate)='saturday' or DATENAME(DW,@StartDate)='sunday'
            BEGIN
                SET  @StartDate=DATEADD(d,1,@StartDate)
                /**Exclude weekends and PH so we do not add numDays here **/
            END
        SET @numDays=@numDays+1
    END
END

/**Omitted some irrelevant codes here**/

/**Add both differences together to get final result**/
DECLARE @result AS DECIMAL(10,2) = @temp + @numDays

RETURN @result

Was thinking to just correct the logic instead of rewriting the entire code. Thanks!

Answer

Like has been mentioned, the best way is to create a Calendar Table and then use the Working Days column, or whatever it is you called it, to calculate the difference.

This is pseudo-SQL, in the absence of a working Calendar Table, but should get you there:

SELECT YT.{YourColumn},
       WD.WorkingDays
FROM YourTable YT
     CROSS APPLY (SELECT COUNT(CT.DateKeyColumn) -1 AS WorkingDays --Minus 1, as we don't want to include the first day
                  FROM CalendarTable CT
                  WHERE CT.[DateColumn] >= YT.StartingDateColumn
                    AND CT.[DateColumn] <= YT.EndingDateColumn
                    AND CT.WorkingDay = 1) WD