Adding workdays

  • Matt Miller (10/18/2007)


    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.

    Sorry Matt, I don't know how I missed that one. That is exactly what I need to do. I never thought about adjusting the start date. I was able to get my code to work as long as the start date was a week day, so I think I will borrow your adjusting the start date part, maybe the whole thing. Thank you so much for your time and effort Matt.

    Greg

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

  • This works as well though it can only go 1 year out. Not sure about the perfomance with the self join.

    declare @startdate datetime, @days int

    set @startdate = '1/1/2007'

    set @days = 20

    select count(*) recnum, a.mydate from

    (select top 365 n,(dateadd(dd,N,@startdate)) as mydate from tally

    where datepart(dw,dateadd(dd,N,@startdate)) not in (1,7) ) a

    join

    (select top 365 n,(dateadd(dd,N,@startdate)) as mydate from tally

    where datepart(dw,dateadd(dd,N,@startdate)) not in (1,7) ) b

    on a.n>=b.n

    group by a.mydate

    having count(*) = @days

    order by a.mydate


  • ..still down the 'you want a dates table' path... 😉

    Assume we have a very simple one. Fulldate and if the day is a holiday or not.

    Haven't tried this on any larger ranges, but since this was just 5 days.. it seems to work ok even when starting on a saturday or sunday....

    create table #dates

    ( fulldate datetime not null, isHoliday bit not null )

    insert #dates

    select '20071015', 0 union all

    select '20071016', 0 union all

    select '20071017', 0 union all

    select '20071018', 0 union all

    select '20071019', 0 union all

    select '20071020', 1 union all

    select '20071021', 1 union all

    select '20071022', 0 union all

    select '20071023', 0 union all

    select '20071024', 0 union all

    select '20071025', 0 union all

    select '20071026', 0 union all

    select '20071027', 1 union all

    select '20071028', 1 union all

    select '20071029', 0

    declare @today char(8)

    set@today = '20071018'

    selectfulldate

    from#dates

    wherefulldate >= @today

    anddatediff(day, @today, fulldate) <= 6

    andisHoliday = 0

    returns the next 5 'workdays' starting with the current day as day one, but skips holidays.

    Good enuff? (it's simple anyway) 😉

    /Kenneth

  • Matt Miller (10/18/2007)


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

    Matt, this works great! I am curious though, why do you have:

    ..CAST(CAST(@Datestart AS DATETIME)AS INT)...?

    I tried it with CAST(@Datestart AS INT) and it worked the same. I don't understand why it is necessary to cast a datetime variable as datetime. Is there a reason why you have it the way you do?

    Greg

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

  • I prefer to have it in there when I'm popping in char values like I was for testing (i.e. not count on the implicit conversion). Sounds like you already have valid datetime values to feed it, so it's not needed.

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

  • That makes sense. I think there is a problem with it though. If I enter '1/2/2008' as @Datestart which is a Wednesday, and 4 as @DaysToAdd, @DateEnd is 1/7/2007, a Monday, when I should expect the answer to be Tuesday. Assuming I do not want to count @Startdate as one of the days. If I add 5 days, though, the answer, 1/9/2007, is correct. It seems to depend on the number of days I want to add. Or maybe I missed something.

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

  • You're right - I'm not accounting for the "extra days" right. the modulo is misapplied there.

    Try this:

    declare @dateStart datetime

    declare @daysToAdd int

    declare @dateEnd datetime

    declare @baseday datetime

    select cast(cast('1/1/1900' as datetime) as int)%7

    select cast(cast('1/2/2008' as datetime) as int)%7

    select @datestart='1/3/2008',@daysToAdd=64

    --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(@datestart as int)%7+@daystoadd%5 =5 then 1

    when cast(@datestart as int)%7+@daystoadd%5 >5 then 2

    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?

  • Matt, I can not tell you how much I appreciate you taking time out of your day to work on this. It seems you are running in to the same problem I was, and that is it will work for certain values of @DaysToAdd, but not others. Your last post works for multiples of 5 only. I tested it up to 1,000,000 days to add and it was still right on. If you try to add only 1 day, however, all bets are off. Same with any other number than a multiple of five. I went ahead and made a function out of it so I could easily see what was happening. If you copy and past the below you will see. In the second column 'NewStart' I should never see a Saturday or Sunday, but if you change the values of @DaysToAdd you will see them. Also, how are you able to post your code like that? Is it an attachment of some kind?

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

    DROP FUNCTION dbo.fnAddWorkdays

    GO

    CREATE FUNCTION dbo.fnAddWorkdays (@DateStart DATETIME,@DaysToAdd INT)

    RETURNS DATETIME

    AS

    BEGIN

    DECLARE @DateEnd DATETIME

    SELECT @DateStart = DATEADD(dd,CASE WHEN CAST(@DateStart AS INT)%7>4

    THEN 7-CAST(@DateStart AS INT)%7 ELSE 0 END,@DateStart)

    SELECT @DateEnd = DATEADD(dd,CASE WHEN CAST(@DateStart AS INT)%7+@DaysToAdd%5 =5 THEN 1

    WHEN CAST(@DateStart AS INT)%7+@DaysToAdd%5 >5 then 2 ELSE 0 END,

    DATEADD(dd,@DaysToAdd%5,DATEADD(wk,@DaysToAdd/5,@DateStart)))

    RETURN @DateEnd

    END

    GO

    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'

    DECLARE @DaysToAdd INT

    SELECT @DaysToAdd = 1 --Just change this number

    SELECT

    Start = DATENAME(dw,start),

    NewStart = DATENAME(dw,dbo.fnAddWorkDays(start,@DaysToAdd)),

    Start,

    NewStart = dbo.fnAddWorkdays(start,@DaysToAdd)

    FROM #test

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

  • Now it just got personal :). Amazing how some good ol' fashion testing seems to work better (I keep trying with not quite enough data to see the flaw, so I apologize).

    here's round # 3 (FYI - use an IFCode markup of CODE and /CODE to get this highlighted,i.e put those between square brackets):

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

    DROP FUNCTION dbo.fnAddWorkdays

    GO

    CREATE FUNCTION dbo.fnAddWorkdays (@DateStart DATETIME,@DaysToAdd INT)

    RETURNS DATETIME

    AS

    BEGIN

    DECLARE @DateEnd DATETIME

    SELECT @DateEnd = dateadd(wk,@daystoAdd/7,dateadd(dd,

    case when cast(dateadd(dd,

    case when cast(@DateStart as int)%7 >4

    then 7-cast(@DateStart as int)%7

    else 0 end,

    @DateStart) as int)%7+@daysToAdd%5>4

    then 2

    else 0 end+@daysToAdd%5,

    dateadd(dd,

    case when cast(@DateStart as int)%7 >4

    then 7-cast(@DateStart as int)%7

    else 0 end,@DateStart)))

    RETURN @DateEnd

    END

    I don't get any saturdays or sundays now. Hopefully third's the charm....

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

  • Matt Miller (10/18/2007)


    I keep trying with not quite enough data to see the flaw

    I Kept doing the same thing. I tested one date against Jeff's code, trying to reverse engineer, and miraculously it worked. Now, as you have it there are no saturdays or sundays, but if you try to add one day you will see what is wrong. If @DateStart is Friday, Saturday, or Sunday, @DateEnd should always be the same. In the case of adding one day it should be Monday for all three days, but it is not. I have thought I cracked it so many times now, only to try some obscure number that breaks it.:w00t:

    Greg

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

  • Actually - I was coding for sat-sun to mean start day =monday, not friday. That's where we have THAT difference. I was rolling the start date forward, not backward.

    You want THIS:

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

    DROP FUNCTION dbo.fnAddWorkdays

    GO

    CREATE FUNCTION dbo.fnAddWorkdays (@DateStart DATETIME,@DaysToAdd INT)

    RETURNS DATETIME

    AS

    BEGIN

    DECLARE @DateEnd DATETIME

    SELECT @DateEnd = dateadd(wk,@daystoAdd/7,dateadd(dd,

    case when cast(dateadd(dd,

    case when cast(@DateStart as int)%7 >4

    then 4-cast(@DateStart as int)%7

    else 0 end,

    @DateStart) as int)%7+@daysToAdd%5>4

    then 2

    else 0 end+@daysToAdd%5,

    dateadd(dd,

    case when cast(@DateStart as int)%7 >4

    then 4-cast(@DateStart as int)%7

    else 0 end,@DateStart)))

    RETURN @DateEnd

    END

    GO

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

  • Matt, I think that did it! I can't test on my real data until I get to work, but on my test table at home it seems to work fine. You're a genious, thank you so much for keeping on it. I think I was almost there, but there is know way I could have done what you did to solve the problem. I keep seeing the modulo used, I guess its time for me to start thinking about it. Thanks again Matt.

    Greg

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

  • mrpolecat (10/18/2007)


    This works as well though it can only go 1 year out. Not sure about the perfomance with the self join.

    declare @startdate datetime, @days int

    set @startdate = '1/1/2007'

    set @days = 20

    select count(*) recnum, a.mydate from

    (select top 365 n,(dateadd(dd,N,@startdate)) as mydate from tally

    where datepart(dw,dateadd(dd,N,@startdate)) not in (1,7) ) a

    join

    (select top 365 n,(dateadd(dd,N,@startdate)) as mydate from tally

    where datepart(dw,dateadd(dd,N,@startdate)) not in (1,7) ) b

    on a.n>=b.n

    group by a.mydate

    having count(*) = @days

    order by a.mydate

    Heck of a good try... but still a bit of RBAR there... looks almost like a date table but limited to 365 days. No doubt it works great on a single date, but what about a column of dates in a table? Add 20 days to a million random dates and see how fast it is... I think it'll crush performance... but not sure...

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

  • Matt... haven't tested it, but I agree... I think you've probably done it and it looks like the performance will outstripe any table solutions in this case... gonna have to put it up against a million rows and see 😉

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

  • Jeff Moden (10/18/2007)


    Heck of a good try... but still a bit of RBAR there... looks almost like a date table but limited to 365 days. No doubt it works great on a single date, but what about a column of dates in a table? Add 20 days to a million random dates and see how fast it is... I think it'll crush performance... but not sure...

    There is an approach which uses WorkingDays table and does not require RBAR processing.

    [Code]

    DECLARE @StartDate datetime

    SET @StartDate = DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)

    -- Table to hold set of dates we are testing

    CREATE TABLE dbo.TestTable (StartDate datetime)

    -- Insertend 100 days to beused for testing

    INSERT INTO dbo.TestTable

    SELECT TOP 100 @StartDate - N

    FROM dbo.Tally

    ORDER BY N

    GO

    -- I create "Working Dates" table from Tally. I don't bother to exclude weekends, it does not matter

    -- I excluded on date interval, it will show if algorithm actually works.

    SELECT IDENTITY(int, 1,1) as ID, N_Date As WorkingDate

    INTO dbo.TestWorkingDays

    FROM dbo.Tally

    WHERE N_Date NOT BETWEEN '20071101' and '20071114'

    ALTER TABLE dbo.TestWorkingDays

    ADD CONSTRAINT PK_TestWorkingDays PRIMARY KEY (ID)

    CREATE INDEX IX_WorkingDate ON dbo.TestWorkingDays (WorkingDate)

    GO

    DECLARE @N int

    SET @N = 30 -- Number of working days we need to add

    SELECT A.StartDate, T2.WorkingDate as EndDate, DATEDIFF(dd, A.StartDate, T2.WorkingDate) as Duration

    FROM TestTable A

    INNER JOIN dbo.TestWorkingDays T1 ON T1.WorkingDate = A.StartDate

    INNER JOIN dbo.TestWorkingDays T2 ON T2.ID = T1.ID + @N

    -- "+ @N" probably is not right. It depend on what do we take as "adding one working day"

    -- If "Adding 1 working day" should result in EndDate = StartDate then it must be "+ @N - 1"

    ORDER BY A.StartDate

    [/Code]

    Key feature here is sequential ID numbers in "Working Days" table.

    In real life it should not be IDENTITY and it must be re-numbered every time working day added/removed to the table.

    But I don't think it's gonna be a significant overhead. How many times Government changed calendar in last 20 years? 😉

    _____________
    Code for TallyGenerator

Viewing 15 posts - 16 through 30 (of 117 total)

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