Discussion on: SQL: Calculate Bussiness Days

CREATE OR ALTER FUNCTION dbo.CalculateBusinessDays (
    @StartDate              date,
    @EndDate                date,
    @BusinessDayOfWeekStart int = 2,
    @BusinessDayOfWeekEnd   int = 6,
    @ExcludeStartDate       bit = 1,
    @ExcludeEndDate         bit = 0
)
RETURNS int
AS
BEGIN
    DECLARE @NumberOfBusinessDays   int = 0;

    IF (/* Date validation */
        @StartDate IS NULL
        OR  @EndDate IS NULL
        OR  @StartDate > @EndDate
    )
    BEGIN /* Error - Let caller know by returning NULL */
        RETURN NULL
    END

    SELECT  /* Exclued Start/End date? */
        @StartDate  = DATEADD(DAY, 1 * @ExcludeStartDate, @StartDate),
        @EndDate    = DATEADD(DAY, -1 * @ExcludeEndDate, @EndDate);

    WITH
        AllDates AS (/* Generate a list of all dates between your @StartDate and @EndDate using a recursive CTE */
            SELECT MyDate = @StartDate
            UNION ALL
            SELECT
                MyDate  = CAST(DATEADD(DAY, 1, MyDate) AS date)
            FROM    AllDates
            WHERE   MyDate < @EndDate
        ),
        BusinessDays AS (/* build a list of business days using recursive CTE */
            SELECT DayOfWeek = @BusinessDayOfWeekStart
            UNION ALL
            SELECT
                DayOfWeek   = DayOfWeek + 1
            FROM    BusinessDays
            WHERE   DayOfWeek < @BusinessDayOfWeekEnd
        )
    SELECT
        @NumberOfBusinessDays   = COUNT(*)  -- Replace this with * to get a list of all of the dates that are business days instead
    FROM    AllDates AS ad
    WHERE
        DATEPART(WEEKDAY, ad.MyDate)IN (SELECT DayOfWeek FROM BusinessDays )

    RETURN @NumberOfBusinessDays
END

-- IF your business has non-consecutive business days like Mon-Tue and Thur-Sat then you can replace the BusinessDays CTE with this one
--BusinessDays AS (/* build a list of business days using hard coded values using a table value contructor */
--  SELECT
--      *
--  FROM ( VALUES (2), (3), (5), (6), (7)) AS BusinessDays (DayOfWeek)
--),
--
--TEST CODE
--Select [dbo].[CalculateBussinessDays]  ('02/18/2021', '03/06/2021')