Adding workdays

  • Sergiy, this goes with my last trigger post, but this is kind of a new topic. I made a test table for emails with a column for delivery_dt as you suggested. I thought all was well, but then I realized I did not know how to add say 5 workdays. Using Jeffs marvelous code for calculating the number of workdays between two dates helped me know how many work days had passed given a startdate, but now I need to be able to set the delivery_dt = startdate plus 5 workdays. I thought it would be easy, but I am completely stumped. I started by making a UDF like so...

    IF OBJECT_ID('dbo.fnAddWorkdays','fn') IS NOT NULL

    DROP FUNCTION dbo.fnAddWorkdays

    GO

    CREATE FUNCTION dbo.fnAddWorkdays (@Startdate DATETIME,@NumberDays INT)

    RETURNS DATETIME

    AS

    BEGIN

    DECLARE @EndDate DATETIME

    SELECT @EndDate = DATEADD(d,@NumberDays,@Startdate)

    SELECT @EndDate = DATEADD(d,

    CASE WHEN DATENAME(dw,@Enddate) = 'Saturday' THEN 2

    WHEN DATENAME(dw,@Enddate) = 'Sunday' THEN 1

    ELSE 0

    END,@EndDate)

    RETURN @Enddate

    END

    SELECT dbo.fnAddWorkdays(GETDATE(),5)

    I quickly realized, though, that it was not going to be that easy. My function only adds 1 or 2 if the enddate is saturday or sunday. It does not account for the weekend days in between. So, anyone have any ideas?

    Greg

    Greg
    _________________________________________________________________________________________________
    The glass is at one half capacity: nothing more, nothing less.

  • This probably isn't the most efficient way but...

    Create a date table (reasonable range)

    weekdays (id, dayDate)

    with only weekday dates.

    FROM invc

    inner join weekdays currDate on invc.mydate = currDate.dayDate --may need both at midnight

    inner join weekdays datePlus5 on currDate.id + 5 = datePlus5.id

    The first inner join may need a caveat if invoices can come in on weekends....

    daryl

  • Thanks Daryl. I want to do it without having to make a days table. I know there is a way, I just can't get my head around it

    Greg
    _________________________________________________________________________________________________
    The glass is at one half capacity: nothing more, nothing less.

  • Ok, I figured it out. I knew it had to be a reverse of Jeff's code, which can be found at

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

    And it was. I had to read the article several times to really understand what was happening, but basically what I needed to do is this

    IF OBJECT_ID('dbo.fnAddWorkdays','fn') IS NOT NULL

    DROP FUNCTION dbo.fnAddWorkdays

    GO

    CREATE FUNCTION dbo.fnAddWorkdays (@Start DATETIME,@NumDays INT)

    RETURNS DATETIME

    AS

    BEGIN

    DECLARE @EndDate DATETIME

    SELECT @EndDate =

    DATEADD(d,@NumDays,@Start) + (DATEDIFF(wk,@Start,DATEADD(d,@NumDays,@Start))*2)

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

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

    RETURN @Enddate

    END

    And I can test it against Jeff's code

    SELECT dbo.fnAddWorkdays('1/1/2007',20) returns 1/27/2007. If I use 1/27/2007 as the end date I can run Jeff's code to get 20

    DECLARE

    @Start DATETIME,

    @End DATETIME

    SELECT @Start = '1/1/2007'

    SELECT @End = '1/27/2007'

    SELECT

    (DATEDIFF(dd, @Start, @End) + 1)-(DATEDIFF(wk, @Start, @End) * 2)

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

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

    Thanks again Jeff. Just another example of how you continue to teach me without even knowing it.

    Greg
    _________________________________________________________________________________________________
    The glass is at one half capacity: nothing more, nothing less.

  • Well...it seems to have some problems. If I start on a week day I should always end on a week day, but my function does not seem to do that. See for yourself. I guess its back to the drawing board.

    IF OBJECT_ID('TempDB..#Test','u') IS NOT NULL

    DROP TABLE #Test

    CREATE TABLE #Test

    (

    Start SMALLDATETIME

    )

    INSERT INTO #Test

    SELECT '1/1/2007' UNION ALL

    SELECT '1/2/2007' UNION ALL

    SELECT '1/3/2007' UNION ALL

    SELECT '1/4/2007' UNION ALL

    SELECT '1/5/2007' UNION ALL

    SELECT '1/6/2007' UNION ALL

    SELECT '1/7/2007' UNION ALL

    SELECT '1/8/2007' UNION ALL

    SELECT '1/9/2007' UNION ALL

    SELECT '1/10/2007' UNION ALL

    SELECT '1/11/2007' UNION ALL

    SELECT '1/12/2007' UNION ALL

    SELECT '1/13/2007' UNION ALL

    SELECT '1/14/2007' UNION ALL

    SELECT '1/15/2007' UNION ALL

    SELECT '1/16/2007' UNION ALL

    SELECT '1/17/2007' UNION ALL

    SELECT '1/18/2007' UNION ALL

    SELECT '1/19/2007' UNION ALL

    SELECT '1/20/2007' UNION ALL

    SELECT '1/21/2007' UNION ALL

    SELECT '1/22/2007' UNION ALL

    SELECT '1/23/2007' UNION ALL

    SELECT '1/24/2007' UNION ALL

    SELECT '1/25/2007' UNION ALL

    SELECT '1/26/2007' UNION ALL

    SELECT '1/27/2007' UNION ALL

    SELECT '1/28/2007' UNION ALL

    SELECT '1/29/2007' UNION ALL

    SELECT '1/30/2007' UNION ALL

    SELECT '1/31/2007'

    SELECT

    Start,

    DayOfWeek1 = DATENAME(dw,start),

    StartAdd5 = dbo.fnAddWorkdays(start,5),

    DayOfWeek2 = DATENAME(dw,dbo.fnAddWorkdays(start,5))

    FROM #Test

    Greg
    _________________________________________________________________________________________________
    The glass is at one half capacity: nothing more, nothing less.

  • Ok, I think I fixed it. I forgot one thing from Jeffs function, then I just fiddled with the numbers until it seemed to work.

    IF OBJECT_ID('dbo.fnAddWorkdays','fn') IS NOT NULL

    DROP FUNCTION dbo.fnAddWorkdays

    GO

    CREATE FUNCTION dbo.fnAddWorkdays (@Start DATETIME,@NumDays INT)

    RETURNS DATETIME

    AS

    BEGIN

    DECLARE @EndDate DATETIME

    SELECT @EndDate =

    (DATEADD(d,@NumDays,@Start)-1) + -- I forgot the -1 here (DATEDIFF(wk,@Start,DATEADD(d,@NumDays,@Start))*2)

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

    +(CASE WHEN DATENAME(dw,@Start) = 'Saturday' THEN 3 ELSE 0 END) --changed the 1 to a 3 and it works

    RETURN @Enddate

    END

    Greg
    _________________________________________________________________________________________________
    The glass is at one half capacity: nothing more, nothing less.

  • False alarm. There is still something wrong with saturdays.

    Greg
    _________________________________________________________________________________________________
    The glass is at one half capacity: nothing more, nothing less.

  • So, what's the deal with not having a dates table?

    It will solve all your problems, no need for complex functions or anything else exotic.

    The main problem: How do you define 'workday' programatically?

    You can't. Simple as that.

    'Workday' is a business rule, it differs from place to place, so you need to single out those 'special' days anyway. Might as well do it once, store it in a permanent table for lookups, then it's always there to be found. No need to try to calculate it everytime.

    Do it once, then forget about it. 🙂

    /Kenneth

  • Kenneth, I probably should refer to it as weekdays, but as far as my production is concerned no weekends are work days. I may end up having to make a table, but I am sure what I am trying to do is possible. If Jeff's code can take a start and end date and count all the week days, that is like saying b - a = X, where a and b are both known, and a is @Start and b is @End and X is an unknown integer. Logically, all I want to do is shift it around to be a + X = b where a is @Start and X is a known integer. Do you see any reason why this would not be so? I am just not skilled enough to do it, but I will keep working on it.

    Greg

    Greg
    _________________________________________________________________________________________________
    The glass is at one half capacity: nothing more, nothing less.

  • Try this one on:

    --set up

    declare @dateStart datetime

    declare @daysToAdd int

    declare @dateEnd datetime

    declare @baseday datetime

    select @datestart='9/1/2007',@daysToAdd=52

    --start by making sure your start day IS a business day

    Select @datestart=dateadd(dd,case when cast(cast(@datestart as datetime) as int)%7 >4 then

    7-cast(cast(@datestart as datetime) as int)%7 else 0 end,@datestart)

    select @datestart

    --the magic

    select @dateEnd=dateadd(dd,

    case when cast(cast(dateadd(dd,@daystoAdd%5,@datestart) as datetime) as int)%7 >4 then

    7-cast(cast(dateadd(dd,@daystoAdd%5,@datestart) as datetime) as int)%7 else 0 end,

    dateadd(dd,@daystoAdd%5,dateadd(wk,@daysToAdd/5,@datestart)))

    select @dateend

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • Kenneth Wilhelmsson (10/18/2007)


    So, what's the deal with not having a dates table?

    It will solve all your problems, no need for complex functions or anything else exotic.

    The main problem: How do you define 'workday' programatically?

    You can't. Simple as that.

    'Workday' is a business rule, it differs from place to place, so you need to single out those 'special' days anyway. Might as well do it once, store it in a permanent table for lookups, then it's always there to be found. No need to try to calculate it everytime.

    Do it once, then forget about it. 🙂

    /Kenneth

    I've found that date tables are fine for RBAR... but, how would you use the date table in this instance of batch code and still have some semblance of performance? Haven't seen a high performance method of using a date table for this, yet.

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

  • Greg Snidow (10/18/2007)


    Kenneth, I probably should refer to it as weekdays, but as far as my production is concerned no weekends are work days.

    Ah, ok. That's another story then. You just want to count all days except weekdays Saturdays and Sundays. Though even that would be easy as pie with a dates table. The reason being that you then don't have to expand all dates within the range, all dates are already there, just waiting to be selected, even without the attribute 'holiday' in said dates table.

    /Kenneth

  • I've found that date tables are fine for RBAR... but, how would you use the date table in this instance of batch code and still have some semblance of performance? Haven't seen a high performance method of using a date table for this, yet.

    Hmmm... maybe you're right, Jeff. Might be trickier than I thought...

    I'll ponder on it ([reminder] must read op more thoroughly.. [/reminder])

    /Kenneth

  • Jeff Moden (10/18/2007)


    Kenneth Wilhelmsson (10/18/2007)


    I've found that date tables are fine for RBAR... but, how would you use the date table in this instance of batch code and still have some semblance of performance? Haven't seen a high performance method of using a date table for this, yet.

    So, Jeff, do you think what I am trying to do is possible, or am I beating a dead horse? I have fiddled with it to get it work for *every* day up to 25 days, then for all time when the start date is a week day, but I am still missing something. I am going nuts, its got to be something so simple.

    Greg

    Greg
    _________________________________________________________________________________________________
    The glass is at one half capacity: nothing more, nothing less.

  • Greg -

    I think you need to try the solution I posted. The reverse engineering you did has some logic flaws. In particular - you're going to start "losing" business days when you add large numbers of business days.

    I broke out the "fixing the start date" if it's not a business day, because that wouldn't be necessary if you can ensure that the start date will NEVER be a non-business day.

    Now - there might be a more elegant way to do it, but I'm fairly confident it's accurate.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

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

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