Add working days but avoid holidays

  • Thom A

    SSC Guru

    Points: 98307

    Just wondering here, what was your reasoning for using a WHILE loop here? Personally I would see that you would get better performance by using a Tally Table and/or Calendar table.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • sterling3721

    SSC Veteran

    Points: 261

    Thank you for sharing. I am looking for something like this. When I tried your script, it complained @increment was not declared. Please update the script.

  • steve 14359

    SSC Enthusiast

    Points: 182

    My original script contained a comment line with a  "Less Than" sign which was interpreted as a opening html tag when uploaded!!

    So I've altered the script but here are the offending lines after correction:-

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

    -- If @N GTE 0 then increment dates while counting

    -- If @N LT 0 then decrement dates while counting

    declare @increment int

    if @n>=0 set @increment = 1 else set @increment = -1

    -- Work out what to do if the loop encounters

    -- a Saturday or Sunday - it depends on the

    -- direction of travel.

    declare @saturdayadjustment int

    declare @sundayadjustment int

  • steve 14359

    SSC Enthusiast

    Points: 182

    Thom A, I could imagine your suggestion might result in better performance if large offsets are added (or subtracted) from a date. However the function was designed for an application where lead times were never more than 20 working days. In such cases I don't think any other approach would make much difference.

    • This reply was modified 4 months, 4 weeks ago by  steve 14359.
  • sterling3721

    SSC Veteran

    Points: 261

    thank you for your update. Logically speaking, after adding @increment to @loopDate, the new @loopDate could still be a holiday(for example, two consecutive holidays on Wed and Thu),  so, the returned @loopDate could be a holiday in this case.

    if exists(select ID from tblHoliday where HolidayDate=@LoopDate)

    begin

    set @LoopDate=DATEADD(DAY,@increment,@LoopDate)

    end

  • Jeff Moden

    SSC Guru

    Points: 994555

    steve 14359 wrote:

    Thom A, I could imagine your suggestion might result in better performance if large offsets are added (or subtracted) from a date. However the function was designed for an application where lead times were never more than 20 working days. In such cases I don't think any other approach would make much difference.

     

    While I seriously appreciate anyone and everyone that will step up and publish a script, I have to agree with Thom on this one.  A lot of people get into a heap of performance trouble when they design only for the current work load.  That's not a slam... just "old dude" advice because I've seen the problem happen so very often.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • dbishop

    Mr or Mrs. 500

    Points: 565

    I have to agree with Jeff for yet another reason. While this particular function was written for this particular application, and you will NEVER be looking more than 20 days either way, the fact that the function is out there, the next person who needs to calculate working days is going to see a function named fns_AddWorkingDays() and say, "Hey, I can just use this one." Unfortunately, he needs to run it in a query that is going to calculate 200 days from an order date against 250K orders and performance is going to SUCK!

  • ScottPletcher

    SSC Guru

    Points: 98087

    Here's a sample function using a physical tally table (it's not worth the trouble to me to try to use an inline tally table within a scalar function).  I've put code to do the one-time create of the tally table in a separate code block.

    SET ANSI_NULLS ON;
    SET QUOTED_IDENTIFIER ON;
    GO
    ALTER FUNCTION [dbo].[fns_AddWorkingDays]
    (
    @StartDate datetime,
    @NumberOfWorkingDays smallint
    )
    RETURNS datetime
    AS
    BEGIN
    RETURN (
    SELECT MAX(WorkDate) AS WorkDate
    FROM (
    SELECT TOP (@NumberOfWorkingDays) DATEADD(DAY, t.number, @StartDate) AS WorkDate
    FROM dbo.tally t
    WHERE
    t.number > 0 AND
    /*0=Monday; 0-4=Mon thru Fri; 5=Sat,6=Sun; regardless of @@DATEFIRST setting.*/
    DATEDIFF(DAY, 0, DATEADD(DAY, t.number, @StartDate)) % 7 <= 4 AND
    NOT EXISTS(SELECT 1 FROM tblHoliday tH WHERE tH.HolidayDate =
    DATEADD(DAY, t.number, CAST(@StartDate AS date)))
    ORDER BY t.number
    ) AS derived
    )
    END
    IF OBJECT_ID('dbo.tally') IS NOT NULL
    DROP TABLE dbo.tally;

    CREATE TABLE dbo.tally (
    number int NOT NULL,
    CONSTRAINT tally__PK PRIMARY KEY CLUSTERED ( number ) WITH ( FILLFACTOR = 100 )
    );
    INSERT INTO dbo.tally VALUES(0);

    ;WITH
    cteTally10 AS (
    SELECT Number FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS Numbers(Number)
    ),
    cteTally100 AS (
    SELECT 1 AS number
    FROM cteTally10 c1
    CROSS JOIN cteTally10 c2
    ),
    cteTally10K AS (
    SELECT 1 AS number
    FROM cteTally100 c1
    CROSS JOIN cteTally100 c2
    ),
    cteTally1Mil AS (
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS number
    FROM cteTally100 c1
    CROSS JOIN cteTally10K c2
    )
    INSERT INTO dbo.tally
    SELECT number
    FROM cteTally1Mil;

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.

  • Jeff Moden

    SSC Guru

    Points: 994555

    p.s.  Since the forum software still provides no easy method back to the article being discussed, here's the link to the original script article, if anyone is interested.

    https://www.sqlservercentral.com/scripts/add-working-days-but-avoid-holidays-2

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • steve 14359

    SSC Enthusiast

    Points: 182

    Comments posted to this topic are about the item Add working days but avoid holidays

  • rchantler

    SSCrazy

    Points: 2091

    We need to do this type of thing but for many countries, some of them whose work week is not Monday to Friday.  Our solution is to maintain an international calendar table.  That's a fair bit of work, but it does simplify calculations such as this one.

  • steve 14359

    SSC Enthusiast

    Points: 182

    I agree. My original coding contained a trap for those who did not realise that “holiday” is just a (very) special case of “non-working time”.

    Anyone who has done this kind of thing for real will almost certainly have been asked for the general case - which specifies resource-specific non-working time (that is non-working time for each employee or physical or service resource). When such a situation becomes dynamic - when the calculation of a date results in that date becoming”non-working” for one or more resources then simplistic solutions involving tally tables become impractical and the maintenance of a table of non-working dates becomes imperative. In the original code the table of holidays becomes a table of non-working dates linked to each resource or coded to apply to “all”. The equivalent of “Saturday” and “Sunday” could also be coded into this table (or a linked table) and the coding could be altered to account for this.

     

  • habib

    SSC Enthusiast

    Points: 182

    i just posted an article that uses a tally table.  Substitute the holiday table with a resource-specific holiday/Non-working table, if needed.

     

  • Jeff Moden

    SSC Guru

    Points: 994555

    habib wrote:

    i just posted an article that uses a tally table.  Substitute the holiday table with a resource-specific holiday/Non-working table, if needed.

    Congratulations but it doesn't help if we don't have a link to your article.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • habib

    SSC Enthusiast

    Points: 182

    Sorry. I should withdraw that comment, at least temporarily.

    The article (Script actually) has not been released for publication - i posted it just today.

     

Viewing 15 posts - 1 through 15 (of 16 total)

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