Business Day Function - Count today as 1

  • Hi, I am having an issue with my below function.  I want it to count the start day as 1 day which it does... but it is messing up if the start date is a holiday.  It is still counting the start date as day 1 even though it should skip it and go to the next day since the start is a holiday.  How do I fix this without messing it up for every other date?

    IE:  if I do 01/01/2018, 14 >> it tells me that 14 business days from 01/01/2018 is 01/19/2018.
    If I do 01/02/2018, 14 >> It works properly and says 14 business days is 01/22/2018.
    Both instances should give me 01/22/2018 as 14 business day
    The Holiday table has standard holidays in it from OPM website.  The date range I have here has 2 holidays.  01/01/2018 and 01/15/2018.


    ALTER FUNCTION [dbo].[fn_ADD_WORKING_DAYS] (
    @DATE  DATE,
    @NDAYS  INT 
    ) RETURNS DATE 
    BEGIN   

       IF @DATE IS NULL
       BEGIN  
        SET @DATE = GETDATE();
       END

       DECLARE @STARTDATE INT = 0
       DECLARE @COUNT  INT = 1
       DECLARE @NEWDATE  DATE = DATEADD(DAY,1,@DATE)             

       WHILE @COUNT < @NDAYS
       BEGIN
        IF DATEPART(WEEKDAY, @NEWDATE) NOT IN (7, 1) AND @NEWDATE NOT IN ( SELECT DT_HOLIDAY FROM TBL_HOLIDAYS )
        SET @COUNT += 1;
        SELECT @NEWDATE = DATEADD(DAY, 1, @NEWDATE), @STARTDATE += 1;
       END

       RETURN DATEADD(DAY, @STARTDATE, @DATE);
    END

    CREATE TABLE [dbo].[TBL_HOLIDAYS](
        [HOLIDAY_ROW_ID] [int] IDENTITY(1,1) NOT NULL,
        [DT_HOLIDAY] [date] NULL
    ) ON [PRIMARY]

    GO

  • The main issue here is that you are using an iterative approach instead of a set-based approach.  Also, inline table valued functions are going to perform much better than scalar functions.  

    I've rewritten your function as an inline table-valued function.  I also used a CTE to create a tally table on the fly.  If you already have a tally table (or Itzik Ben Gan's tally table function) you can skip the CTE.

    I've also used OFFSET/FETCH to return the desired date.

    CREATE FUNCTION [dbo].[fn_ADD_WORKING_DAYS] (
    @DATE DATE,
    @NDAYS INT 
    ) RETURNS TABLE WITH SCHEMABINDING
    AS RETURN
    WITH Tally_Base AS
    (
        SELECT n
        FROM ( VALUES(0), (0), (0), (0), (0), (0), (0), (0),(0), (0) ) b(n)
    )
    , Tally AS
    (
        SELECT ROW_NUMBER() OVER(ORDER BY @@VERSION) n
        FROM Tally_Base A
        CROSS JOIN Tally_Base B
        /* Add as many cross joins as necessary to get the maximum number of records you will need. */
    )

    /* If you already have a tally table in your database, you can skip the above CTE. */

    SELECT dt
    FROM Tally
    CROSS APPLY( VALUES(DATEADD(DAY, n - 1, @Date))) dt(dt)
    WHERE dt.dt NOT IN ( SELECT Dt_Holiday FROM Tbl_Holidays )
        AND DATEPART(WEEKDAY, dt) NOT IN (1, 7)
    ORDER BY dt.dt
    OFFSET @NDAYS - 1 ROWS
    FETCH NEXT 1 ROWS ONLY

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • Thank you, wow!  I thought I was pretty good at SQL... guess not. 😉

    So I can learn, what is the tally table doing exactly?

  • amy26 - Wednesday, February 14, 2018 8:50 AM

    Thank you, wow!  I thought I was pretty good at SQL... guess not. 😉

    So I can learn, what is the tally table doing exactly?

    The tally table is just a list of consecutive numbers that replaces incrementing a variable in an iterative approach.  You can learn more about it here: Tally OH!

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • drew.allen - Wednesday, February 14, 2018 9:16 AM

    amy26 - Wednesday, February 14, 2018 8:50 AM

    Thank you, wow!  I thought I was pretty good at SQL... guess not. 😉

    So I can learn, what is the tally table doing exactly?

    The tally table is just a list of consecutive numbers that replaces incrementing a variable in an iterative approach.  You can learn more about it here: Tally OH!

    Drew

    Thank you so much!  Always something new to learn. 🙂

  • I find that it's easier to learn about Tally Tables in this article from Dwain Camps:
    Tally Tables in T-SQL « dwaincsql

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply