Post reply

Calculating Work Days

  • December 31, 2008 at 7:03 pm

    #919888

    Fraggle (10/24/2008)

    Jeff,

    Alright, now that I have had a little time to ponder the imponderable as my dad would say, I figured out how to add X number of business days to a start date.

    Create Function fn_AddBusinessDays

    (@StartDate DATETIME,

    @DaysToAdd TINYINT

    )

    Returns DATETIME

    AS

    BEGIN

    DECLARE @EndDate DATETIME

    SET @EndDate = DateAdd(Week, @DaysToAdd/5, @StartDate)

    + CASE

    WHEN DatePart(dw, @StartDate) + @DaysToAdd % 5 >= 7

    THEN @DaysToAdd % 5 + 2

    ELSE @DaysToAdd % 5 --else justadd the day

    END

    RETURN CASE

    WHEN DATENAME(dw, @EndDate) = 'Saturday'

    THEN DateAdd(dd, 2, @EndDate)

    WHEN DATENAME(dw, @EndDate) = 'Sunday'

    THEN DATEADD(dd,1,@EndDate)

    ELSE @EndDate

    End

    END

    See what a little time will do. No RBAR!

    Fraggle

    Hey there, Fraggle... I know it's been a while, but I thought that I'd tell you that not only did I test your good function, but I've used it quite a few times. The cool thing about it is that you can easily use it on a whole bunch of dates. When you trying to do the same thing with a calendar table, you have to do a subquery with something like ...

    SELECT MIN(calen_dt) FROM dbo.Calender WHERE calen_dt >= DATEADD(d, @intErval, @dteCurrentDate) AND flg_bdate='Y'

    Now, I'll admit that your function doesn't handle holiday's like a calendar table might... but if you had a nice holiday table...

    Anyway, thanks again for the function.

    --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.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • December 31, 2008 at 11:58 pm

    #919903

    Jeff,

    Glad to hear that you are getting some use out of it. It get's used about 30-40 times a day to update work tickets in our system.

    Me personally, I would just do a lookup on the Calendar table to find out if the day is a Holiday. If performance was bad, I would try a non-clustered index and see if that helps at all. (year, month, day, holiday). Could actually do some logic to verify you are actually in a month with a holiday (no holiday in June).

    Fraggle

  • January 2, 2009 at 7:10 am

    #920255

    These days, with furloughs a popular tool for profitability, a flexible approach is necessary.

    Holidays have to be managed by the company, not just by logic. I think a two-step process is in order.

    Start by importing from a web service or other source the holidays your company observes. http://www.holidaywebservice.com/holidays/HolidayService.asmx might be suitable.

    Do it for a 50- or 100-year period, whatever suits.

    Edit these if necessary. This gives you a start.

    Then, row by agonizing row, add the special holidays or furloughs your company has observed and plans to observe. Or, if you plan to offload this to somebody in HR, create an interface.

    Keep the list in a table or in an xml file.

    Whichever way you do your calculations, consult this list.

  • February 18, 2009 at 3:39 am

    #945772

    Ramesh Lende (1/10/2006)

    I know everyone is talking about Holidays For this particular function. but guys don't worry to consider holidays in this is very easy. I have modified this SQL little bit and it would take care of holidays also, even though holiday comes on weekend.

    First Create Holiday Table

    create table HolidayTable (HolDay datetime)

    Insert two Holidays in it. (One on weekday and one on weekend just to

    make sure function does consider weekend holidays)

    insert into HolidayTable values ('01/03/2006')

    insert into HolidayTable values ('01/08/2006')

    Now change SQL to

    SELECT

    (DATEDIFF(dd, @StartDate, @EndDate) + 1)

    -(DATEDIFF(wk, @StartDate, @EndDate) * 2)

    -(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)

    -(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END)

    - ( Select count(*) from HolidayTable where HolDay between @StartDate and @EndDate and

    (datepart(dw,HolDay) <> 1 and datepart(dw,HolDay)<>7 ))

    and you are ALL SET.

    Hope this helps,

    Best Regards,

    Ramesh.

  • February 18, 2009 at 3:40 am

    #945773

    It was really fantastic thank you so much....

  • February 19, 2009 at 4:25 am

    #946554

    Excellent post Jeff. I had written a function to calculate business days about 3 1/2 years ago for a client of mine (http://qa.sqlservercentral.com/scripts/Miscellaneous/31543/). Yours is a bit more elegant. I had to have a variable work week parameter and include holidays that were stored in a holiday table. I couldn't think of a way at the time to get an accurate answer for every scenario other than the solution that I implemented. I may have to refactor my original to be non-RBAR using the technique that you demonstrated.


    Karen Gayda
    MCP, MCSD, MCDBA

    gaydaware.com

  • February 19, 2009 at 8:51 am

    #946806

    Thanks for the feedback, Karen. I know there's a lot of them, but some of the posts in the discussions in this thread show some pretty nifty ways to handle the problem, as well.

    --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.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • February 19, 2009 at 9:26 am

    #946841

    I don't think our PeopleSoft Admin has gotten around to this, so I thought I'd drop a line. She found your routine quite elegant and saved her a lot of work on a project she initiated to help HR/Payroll with a process which is currently very manual intensive.

  • March 2, 2009 at 8:44 pm

    #952207

    Lynn Pettis (2/19/2009)

    I don't think our PeopleSoft Admin has gotten around to this, so I thought I'd drop a line. She found your routine quite elegant and saved her a lot of work on a project she initiated to help HR/Payroll with a process which is currently very manual intensive.

    Dang... sorry I missed this. Thanks for the feedback, Lynn.

    --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.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • March 6, 2009 at 3:04 pm

    #955299

    Hi,

    Can any one help in calculating wordays starting from monday to friday

    right now I am using (DATEDIFF(DAY, DATES.StartDate, DATES.EndDate)+1) to get monday to sat but

    what to do to get from monday to friday ..

  • March 6, 2009 at 3:24 pm

    #955309

    bindu.muttavarapu (3/6/2009)

    Hi,

    Can any one help in calculating wordays starting from monday to friday

    right now I am using (DATEDIFF(DAY, DATES.StartDate, DATES.EndDate)+1) to get monday to sat but

    what to do to get from monday to friday ..

    I believe you may have missed the article this thread is attached to... please see the following...

    http://qa.sqlservercentral.com/articles/Advanced+Querying/calculatingworkdays/1660/

    --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.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • May 4, 2009 at 9:31 am

    #988155

    Just tried this out....good job!

    Or in SoCal parlance: "Dude, you rock!":cool:

  • May 4, 2009 at 11:10 am

    #988217

    James A. Lawrence (5/4/2009)

    Just tried this out....good job!

    Or in SoCal parlance: "Dude, you rock!":cool:

    Great to hear, James. Thanks for taking the time to post a reply. 🙂

    --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.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • May 4, 2009 at 11:11 am

    #988219

    Lynn Pettis (2/19/2009)

    I don't think our PeopleSoft Admin has gotten around to this, so I thought I'd drop a line. She found your routine quite elegant and saved her a lot of work on a project she initiated to help HR/Payroll with a process which is currently very manual intensive.

    I don't know how I missed this, Lynn. Thanks for the feedback.

    {EDIT} Wow! Deju vu, huh? More coffee, please.

    --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.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • October 6, 2009 at 4:48 am

    #1062164

    I calculate wokringdays like this:

    SELECT days/7*5 + days%7

    - CASE WHEN 6 BETWEEN wd AND wd + days%7-1 THEN 1 ELSE 0 END

    - CASE WHEN 7 BETWEEN wd AND wd + days%7-1 THEN 1 ELSE 0 END

    FROM (SELECT

    DATEDIFF(day, @StartDate, @EndDate) + 1 AS days,

    DATEPART(weekday, @StartDate + @@DATEFIRST - 1) AS wd

    ) AS D)

Viewing 15 posts - 121 through 135 (of 156 total)

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