SQL Server, Get Business Days Function

We have a database for functions so they generic ones can be shared across multiple databases.  This is one I created for business days.

First you need to create a holiday table or non-shipping days table and put your companies dates in it.  I went ahead and put around 10 years of dates.  Most dates are easy to figure out if you go look up the holiday on the web.

Once you have this table you are ready for your function, below is the code..

USE [PTNFunctions]

GO

/****** Object:  UserDefinedFunction [dbo].[fn_GetExpectedShipDate]

        Script Date: 11/09/2012 13:49:11 ******/

--select ptnfunctions.dbo.fn_GetBusinessDay('11/27/2012', -3)

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER OFF

GO

alter function [dbo].[fn_GetBusinessDay]

(

    @StartDate DATETIME,

    @NumberOfBusinessDays INT = 1

)

returns DATETIME

AS

BEGIN

DECLARE @BusinessDate DATETIME

DECLARE @BusinessDayCount INT

DECLARE @IncrementalDay INT

    SELECT @BusinessDayCount = 0

    SELECT @BusinessDate = @StartDate

    IF @NumberOfBusinessDays > 0

    BEGIN

        SELECT @IncrementalDay = 1

    END

    ELSE IF ( @NumberOfBusinessDays < 0 )

    BEGIN

        SELECT @IncrementalDay = -1

    END

    SELECT @NumberOfBusinessDays = Abs(@NumberOfBusinessDays)

    ----------------------------------------------------

    --add or subtract days to the current date based off number 

    --of business days, may work out this way

    ----------------------------------------------------

    WHILE (@BusinessDayCount < @NumberOfBusinessDays)

    BEGIN

        SELECT @BusinessDate = @BusinessDate + @IncrementalDay

        IF(datename(weekday,@BusinessDate) != 'Saturday' 

            AND datename(weekday,@BusinessDate) != 'Sunday')

        BEGIN

            IF(NOT EXISTS(SELECT * FROM 

                            Holidays (nolock) 

                            WHERE HolidayDate=

                                    convert(DATETIME,Convert(nvarchar(20),@BusinessDate,101))))

            BEGIN 

                SELECT @BusinessDayCount = @BusinessDayCount + 1

            END

        END

    END

return @BusinessDate

end

The power of this is in the use when you need to get back a select statement based off the number of business days like this..

 

SELECT 

    * 

FROM 

    yourTableHere cm (nolock)

WHERE 

    cm.yourFieldHere=3

    AND cm.CREATEDDATE >= ptnfunctions.dbo.fn_GetBusinessDay(cm.CREATEDDATE, 3)

Or you need to use it in the select part to return number of business days for a sql statement, or report..

SELECT 

    ptnfunctions.dbo.fn_GetBusinessDay(cm.CREATEDDATE, 3) AS '3BusinessDays', * 

FROM 

    yourTableHere cm (nolock)

WHERE 

    cm.yourFieldHere = 1