Calculating Business Days of a Month Excluding Holidays in SQL Using a Custom Function

Calculating Business Days of a Month (Excluding Holidays) in SQL

Calculating the business days of a month, excluding holidays, is a common requirement in various industries such as finance, retail, and healthcare. In this article, we will explore how to achieve this using SQL.

Understanding the Problem Statement

The problem statement asks us to write a query that returns the current working day of a month and the time gone, which can be calculated by dividing the working days of a particular month by the total number of working days in that month. The query should exclude all holidays from the calculation.

Creating a Table with Public Holidays

To solve this problem, we need to create a table with public holidays, including the day name and date. We will use this table as a reference to exclude holidays from our calculations.

CREATE TABLE [holidays](
    [DayName] [varchar](20) NOT NULL,
    [Date] [datetime] NOT NULL,
    [Weekday] [varchar](1) NOT NULL
);

-- 2018 Bank Holidays
INSERT [holidays] ([DayName], [Date], [Weekday]) VALUES
    (N'New Years Day','2018-Jan-01','Y'),
    (N'Good Friday','2018-Mar-30','Y'),
    (N'Easter Monday','2018-Apr-02','Y'),
    (N'Early May BH','2018-May-07','Y'),
    (N'Spring BH','2018-May-28','Y'),
    (N'Summer BH','2018-Aug-27','Y'),
    (N'Christmas Day','2018-Dec-25','Y'),
    (N'Boxing Day','2018-Dec-26','Y');

Creating the `WorkDayCount` Function

We will create a function called WorkDayCount that takes two parameters: @StartDate and @EndDate. This function will return the number of working days between the start date and end date, excluding holidays.

CREATE FUNCTION [WorkDayCount](
    @Startdate datetime,
    @EndDate datetime)
RETURNS INT
AS
BEGIN
DECLARE 
    @Holidays int,
    @WorkDays int

SELECT @Holidays = COUNT(distinct Date) FROM HOLIDAYS 
WHERE Date BETWEEN @StartDate AND @EndDate AND [Weekday]='Y'

SELECT @WorkDays = ((
    -- Days between start and end dates
    (DATEDIFF(dd, @StartDate, @EndDate))
    -- Minus the number of weekend days between start and end dates
    -(DATEDIFF(wk, @StartDate, @EndDate) * 2)
    -- Minus an extra day if starting on a Sunday
    -(CASE datepart(dw, @StartDate)+@@datefirst WHEN 8 THEN 1 ELSE 0 END) 
    -- Minus an extra day if ending on a Saturday
    -(CASE datepart(dw, @EndDate)+@@datefirst WHEN 7 THEN 1 WHEN 14 THEN 1 ELSE 0 END))
    -- Minus Holiday Days
    - @Holidays)
RETURN @WorkDays
END

Using the `WorkDayCount` Function in a Query

We can now use the WorkDayCount function in our query to calculate the working days of a particular month. We will join the holidays table to our main table using the date column.

SELECT 
    DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1) AS StartDate,
    DATEADD(month, DATEDIFF(month, 0, GETDATE()), 1) AS EndDate,
    WorkDayCount(StartDate, EndDate) AS WorkingDays

This query will return the start and end dates of the current month, along with the number of working days in that month.

Example Use Case

Suppose we want to calculate the working days of February 2023. We can use the following query:

SELECT 
    DATEADD(month, DATEDIFF(month, 0, '2022-02-01') - 1) AS StartDate,
    DATEADD(month, DATEDIFF(month, 0, '2022-02-28'), 1) AS EndDate,
    WorkDayCount(StartDate, EndDate) AS WorkingDays

This query will return the start and end dates of February 2023, along with the number of working days in that month.

Conclusion

Calculating the business days of a month, excluding holidays, is an important requirement in various industries. By creating a table with public holidays and using a function to calculate the working days, we can achieve this requirement using SQL. The WorkDayCount function provides a flexible way to calculate the working days between two dates, excluding holidays.

Last modified on 2023-12-30