A while ago I helped a colleague develop a T-SQL function that would increment a date by an integer value and return the first working day following and closest to the resulting date.  The requirements were not complex - a date is incremented by a number of days and the next working date identified.  The only complexity was how to make sure that the date did not fall on a weekend or “non-working” days such as public or national holidays.

The first step would be to have a table that stores what I will call non-working days.  The table was filled with samle data which incidentally are Maltese Public and National holidays (obtained from the Department of Information - Malta website).  I am also adding an “extra” day which is described as a “Family Activities Day”, but of course you may add as many as necessary depending on the business area.

USE [tempdb]
GO

-- table to hold Non-Working Days (such as Public/National holidays)
CREATE TABLE [dbo].[tb_nonworkingdays] (
    nwd_pk int IDENTITY(1,1) NOT NULL,
    nwd_date datetime NOT NULL,
    nwd_description nvarchar(255) NOT NULL,
    nwd_annualevent bit NOT NULL DEFAULT (1)
)
GO

-- create sample data - based on holidays for 2012 in Malta
-- source: http://www.doi.gov.mt/en/archive/publicholidays/pholidays12.asp
SET NOCOUNT ON;

-- annual repeatable events
INSERT INTO [dbo].[tb_nonworkingdays] (
    nwd_date, nwd_description, nwd_annualevent
    )
    SELECT '2012-01-01', 'New Year''s Day', 1 UNION ALL
    SELECT '2012-02-10', 'Feast of St. Paul''s Shipwreck', 1 UNION ALL
    SELECT '2012-03-19', 'Feast of St. Joseph', 1 UNION ALL
    SELECT '2012-03-31', 'Freedom Day', 1 UNION ALL
    SELECT '2012-05-01', 'Worker''s Day', 1 UNION ALL
    SELECT '2012-06-07', 'Sette Giugno', 1 UNION ALL
    SELECT '2012-06-29', 'Feast of St. Peter and St. Paul', 1 UNION ALL
    SELECT '2012-08-15', 'Feast of the Assumption', 1 UNION ALL
    SELECT '2012-09-08', 'Feast of Our Lady of Victories', 1 UNION ALL
    SELECT '2012-09-21', 'Independence Day', 1 UNION ALL
    SELECT '2012-12-08', 'Feast of the Immaculate Conception', 1 UNION ALL
    SELECT '2012-12-13', 'Republic Day', 1 UNION ALL
    SELECT '2012-12-25', 'Christmas Day', 1;
GO

-- one-off annual events
INSERT INTO [dbo].[tb_nonworkingdays] (
    nwd_date, nwd_description, nwd_annualevent
    )
    SELECT '2012-04-06', 'Good Friday', 0;
GO

-- one-off company events, etc.
INSERT INTO [dbo].[tb_nonworkingdays] (
    nwd_date, nwd_description, nwd_annualevent
    )
    SELECT '2012-04-09', 'Family Activities Day', 0;
GO

The function first checks the input parameters, then increments the @StartDate variable by the number of days that have to be added.  Further checks verify whether the date falls on a weekday, and if the date is one of the pre-defined non-working days.  If either of these conditions fail the date is incremented further by another day.  These checks are repeated until the calculated date is valid.

CREATE FUNCTION [dbo].[fn_NextWorkingDay] (@StartDate as datetime, @NoDays int)
RETURNS datetime 
AS
BEGIN
    IF (@StartDate IS NULL) OR (@NoDays IS NULL)
        RETURN NULL;

    DECLARE @NextDate datetime;
    DECLARE @DateFound bit;

    SET @DateFound = 0;
    -- increase the @StartDate by the @NoDays value to reduce iterations 
    -- from the below
    SET @NextDate = DATEADD(d, @NoDays, @StartDate);

    WHILE (@DateFound = 0)
    BEGIN
        -- if the date is between Mon and Fri
        IF DATEPART(weekday, @NextDate) IN (2,3,4,5,6)
        BEGIN
            IF EXISTS(
                SELECT 1 FROM dbo.tb_nonworkingdays WHERE nwd_date = @NextDate)
            BEGIN
                -- add one day if the date is a Public/National holiday
                SET @NextDate = DATEADD(d, 1, @NextDate); -- add one day
            END
            ELSE
            BEGIN
                -- the next date has been identified
                SET @DateFound = 1;
            END
        END
        ELSE
        BEGIN
            -- if the date is NOT between Mon and Fri (i.e. falls on a weekend)
            SET @NextDate = DATEADD(d, 1, @NextDate); -- add one day
        END
    END

    RETURN (@NextDate);
END
GO

As you can see the function is quite simple. The functionality can be extended further for other business areas and rules, such as for example:

  • In the catering inductry both checks for weekdays and holidays would probably not apply;
  • Most hair salons in Malta work Tuesdays to Saturdays;
  • Teachers’ non-working days include the Easter, Summer and Christmas periods (not that they don’t work during these days…);
  • The function can be made to exclude an employee’s vacation leave bookings.

Other than that I’m sure it is a good starting point for the above-mentioned scenarios, or more.