SQL: Calculate Bussiness Days

#sql #sqlserver #mysql

In this example I'm going to give the query to find the business days between two days.

Use this function to calculate the number of business days excluding Saturday and Sunday. Also it will exclude start date and it will include end date.

go
-- Select [dbo].[CalculateBussinessDays]  ('02/18/2021', '03/06/2021')
CREATE or ALTER FUNCTION [dbo].[CalculateBussinessDays] ( 
    @StartDate DATETIME,
    @EndDate  DATETIME 
)
returns INT AS
BEGIN
  DECLARE @tempStartDate     DATETIME= @StartDate;
  DECLARE @tempEndDate       DATETIME = @EndDate;

  IF(@tempStartDate IS NULL
  OR
  @tempEndDate IS NULL)
  BEGIN
    RETURN NULL;
  END
  --To avoid negative values reverse the date if StartDate is grater than EndDate
  IF(@StartDate > @EndDate)
  BEGIN
    SET @StartDate = @tempEndDate;
    SET @EndDate = @tempStartDate;
  END

  DECLARE @Counter           INT = Datediff(day,@StartDate ,@EndDate);
  DECLARE @TempCounter       INT = 0;
  DECLARE @TotalBusinessDays INT = 0;

  WHILE @Counter >= 0
  BEGIN
    IF(@TempCounter > 0 OR @Counter = 1) -- To ignore first day's calculation
    Begin
        SET @TotalBusinessDays = @TotalBusinessDays 
                                        + (Iif(Datename(dw, Dateadd(day,@TempCounter,@StartDate)) 
                                            IN('Monday', 'Tuesday','Wednesday','Thursday','Friday'),1,0))
    END

    SET @Counter = @Counter - 1
    SET @TempCounter = @TempCounter +1
  END
  RETURN @TotalBusinessDays;
END

Top comments (2)

Nick Fotopoulos • Feb 25 '21 • Edited

Nice! I needed a slightly more flexible version so I refactored to include parameters for defining which days are business days, and to exclude the first or last day. Instead of a loop I used a recursive CTE to get the dates so it could be joined for extra flexibility. You can easily modify this to be a procedure that returns all of the business days as a result set of dates. Just change the select list at the end to be * instead of @NumberOfBusinessDays = COUNT(*)
EDIT: Wrote this on SQL Server, not sure if there is anything T-Sql specific here that might not work on MySQL.

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')

Santhosh N • Feb 26 '21

Awesome!!
Thanks lot for taking your time to do this.

DEV Community

SQL: Calculate Bussiness Days

Top comments (2)

Read next

Dynamic SQL

%TYPE and %ROWTYPE Attributes in PLSQL

BULK COLLECT in PL/SQL

Spring Data JPA Method Naming Conventions: Build Queries Without Writing SQL