SQL Server Performance

4 UDFs to manipulate Working/Business days

Discussion in 'Contribute Your SQL Server Scripts' started by Michael_D, Sep 16, 2005.

  1. Michael_D New Member


    All following functions requare a Holidays table.
    It should contain a field called Holiday of datetime datatype.

    It might contain other fields like :

    HolidayName Varchar(50) or whatever you need.


    CREATE TABLE [Holidays] (
    [Holiday] [datetime] NOT NULL)



    Insert your Nonworking days (except weekends)in the table
    For example:


    INSERT INTO [Holidays]
    VALUES('2005-12-24')






    /********************************************************************************************
    ****
    **** returns next working day
    ****
    **********************************************************************************************/
    CREATE FUNCTION fNextWorkingDay (@Start datetime)
    RETURNS datetime
    AS
    BEGIN
    DECLARE @NextDay datetime

    -- First, add a single day

    SET @NextDay = DATEADD(d, 1, @Start)

    -- Now, depending on the day of the week, move it forward
    -- to the next weekday. Note this assume -- s that the first
    -- day of the week is Sunday, the defaul -- t.

    SET @NextDay = CASE DATEPART(dw, @NextDay)
    WHEN 1 THEN DATEADD(d, 1, @NextDay)
    WHEN 7 THEN DATEADD(d, 2, @NextDay)
    ELSE @NextDay
    END

    -- Now look up the date in the holiday table, looping
    -- to the next clear weekday.
    WHILE EXISTS(SELECT Holiday FROM Holidays WHERE Holiday = @NextDay)
    BEGIN
    SET @NextDay = DATEADD(d, 1, @NextDay)
    SET @NextDay = CASE DATEPART(dw, @NextDay)
    WHEN 1 THEN DATEADD(d, 1, @NextDay)
    WHEN 7 THEN DATEADD(d, 2, @NextDay)
    ELSE @NextDay
    END
    END

    -- Finally, return the next working date
    RETURN @NextDay
    END



    /********************************************************************************************
    ****
    **** returns previous working day
    ****
    **********************************************************************************************/
    CREATE FUNCTION fPreviousWorkingDay (@Start datetime)
    RETURNS datetime
    AS
    BEGIN
    DECLARE @PrevDay datetime

    -- First, add a single day

    SET @PrevDay = DATEADD(d, -1, @Start)

    -- Now, depending on the day of the week, move it backward
    -- to the next weekday. Note this assume -- s that the first
    -- day of the week is Sunday, the defaul -- t.

    SET @PrevDay = CASE DATEPART(dw, @PrevDay)
    WHEN 1 THEN DATEADD(d, -2, @PrevDay)
    WHEN 7 THEN DATEADD(d, -1, @PrevDay)
    ELSE @PrevDay
    END

    -- Now look up the date in the holiday table, looping
    -- to the next clear weekday.

    WHILE EXISTS(SELECT Holiday FROM Holidays WHERE Holiday = @PrevDay)
    BEGIN
    SET @PrevDay = DATEADD(d, -1, @PrevDay)
    SET @PrevDay = CASE DATEPART(dw, @PrevDay)
    WHEN 1 THEN DATEADD(d, -2, @PrevDay)
    WHEN 7 THEN DATEADD(d, -1, @PrevDay)
    ELSE @PrevDay
    END
    END



    -- Finally, return the next working date

    RETURN @PrevDay
    END


    /********************************************************************************************
    **** returns date after given number of working days
    **** uses function fNextWorkingDay
    ****
    **********************************************************************************************/
    CREATE FUNCTION fAddWorkingDay (@Start datetime, @NumOfDays integer)
    RETURNS datetime
    AS
    BEGIN
    DECLARE @NextDay datetime
    DECLARE @Counter int

    IF @NumOfDays>0
    BEGIN
    SET @Counter=1
    -- Drop the time part and initialize the Return Variable

    SET @NextDay=CONVERT(datetime ,(CONVERT(char(10),@Start,102)),102 )

    WHILE @Counter <= @NumOfDays
    BEGIN
    SET @NextDay = dbo.fNextWorkingDay(@NextDay)
    SET @Counter=@Counter+1
    END
    END
    ELSE
    BEGIN
    SET @Counter=ABS(@NumOfDays)
    -- Drop the time part and initialize the Return Variable

    SET @NextDay=CONVERT(datetime ,(CONVERT(char(10),@Start,102)),102 )

    WHILE @Counter>0
    BEGIN
    SET @NextDay = dbo.fPreviousWorkingDay(@NextDay)
    SET @Counter=@Counter-1
    END
    END

    RETURN @NextDay
    END


    /********************************************************************************************
    **** returns number of working days between given dates
    **** (negative values indicates then the second date is earlier than the first one)
    ****
    **********************************************************************************************/

    CREATE FUNCTION fWorkingDaysBetween (@StartDay datetime, @EndDay datetime)
    RETURNS int
    AS
    BEGIN

    DECLARE
    @date1 datetime,
    @date2 datetime,
    @tempdate datetime,
    @day int,
    @count int,
    @multiplier int

    IF @StartDay < @EndDay
    BEGIN
    SET @date1 = CONVERT(datetime ,(CONVERT(char(10),@StartDay,102)),102 )
    SET @date2 = CONVERT(datetime ,(CONVERT(char(10),@EndDay,102)),102 )
    SET @multiplier=1
    END
    ELSE
    BEGIN
    SET @date2 = CONVERT(datetime ,(CONVERT(char(10),@StartDay,102)),102 )
    SET @date1 = CONVERT(datetime ,(CONVERT(char(10),@EndDay,102)),102 )
    SET @multiplier=-1
    END

    SET @tempdate = @date1
    SET @count = 0

    /* Process till tempdate becomes date2 */
    WHILE ( datediff(dd,@tempdate,@date2) >= 0)
    BEGIN
    SET @day = Datepart(dw,@tempdate)
    /* If it is not a Saturday or a Sunday */
    IF (@day != 1 AND @day != 7)
    /* If it is not a Public Holiday */
    IF NOT EXISTS(SELECT * FROM holidays WHERE holiday= @tempdate)
    /* Increment @tempdate by 1 day */
    SELECT @count = @count + 1

    ET @tempdate = Dateadd(dd,1,@tempdate)
    END

    RETURN @count * @multiplier
    END





    Wise men talk because they have something to say; fools, because they have to say something.

    Plato
  2. quimeralavida New Member

    Hello Mike and thank you for this post, now is a bit back in years but see if you are still around

    I am fearly new to SQL and when I try to execute the function for previous working day it keeps asking
    a value for @Start. Could you please clarify this? I have not seen any value allocated to this variable
    at all. Again I am very new to SQL and perhaps I am missing / not aware of something?
    Arthur

Share This Page