Add working days but avoid holidays

  • Thom A

    SSC Guru

    Points: 98011

    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: 254

    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: 181

    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: 181

    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 3 weeks, 2 days ago by  steve 14359.
  • sterling3721

    SSC Veteran

    Points: 254

    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: 993623

    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 an 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

  • steve 14359

    SSC Enthusiast

    Points: 181

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

  • dbishop

    Mr or Mrs. 500

    Points: 559

    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!

Viewing 8 posts - 1 through 8 (of 8 total)

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