Adding workdays

  • Greg Snidow (10/21/2007)


    Kenneth, I am using it for setting the delivery date for email notification in an email staging table. I am experimenting with a set up suggeted by Sergiy in my post a few days ago called 'trigger help'. Anyhow, I need to be able to set a delivery date of an email based on the enter date of a record. It just so happens this person wanted 5, 10, and 20 day notification using week days only. It just as well could have been 37 weekdays, or 63, or whatever it needs to be.

    So, you need to add not working days but weekdays.

    Why did not you ask for it from the beginning?

    😉

    You know, with 5, 10 and 20 days options it's quite easy:

    5 weekdays = 7 calendar days, 10 = 14, 20 = 28.

    🙂

    If you expect these numbers to be changed you need to follow this (let's take 12 as an example):

    1) convert number of whole weeks to calendar days - 10 wd is 2 whole weeks - 14 days;

    2) Add remainder to the (1) result;

    3) figure out which day of the week you're in.

    This will return 1 for Monday ... 7 for Sunday independently of DATEFIRST settings:

    [font="Courier New"](@@Datefirst + Datepart(dw, YourDate) - 2) %7 +1[/font]

    4) if the remainder (2 in our example) is > 5 - Current Weekday then add 2 days, else leave the number as it is.

    If to translate it into T-SQL it's gonna be something like this:

    [Code]

    SELECT CreateDate + @NotificationDays%5*7 + @NotificationDays/5 +

    CASE WHEN (@@Datefirst + Datepart(dw, CreateDate) - 2) %7 +1 + @NotificationDays/5) > 5

    THEN 2 ELSE 0 END As NotificationDate

    From inserted

    [/Code]

    NB. Don't trust my typing! I don't have a chance to check it now, so do it yourself.

    _____________
    Code for TallyGenerator

  • Michael Meierruth (10/22/2007)


    How to you select/copy/paste the sample code in the gray background areas?

    When I do this I don't get any line breaks.

    I know... pretty bad, huh? Hope they fix that soon.

    It does have "linefeed" or "newline" characters. If you copy'n'paste into MS Word and do a search'n'replace from ^l (circumflex small "L") to ^p (circumflex small "P"), you get the necessary line breaks (carriage return). Then, copy'n'paste from Word to whatever.

    Leading space will still be lost, though... makes a formatting mess... but the code will work.

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

  • Kenneth Wilhelmsson (10/22/2007)


    Greg,

    ok, and how are these mails staged?

    one-by-one? By the thousands? (or millions?)

    Are you looking for the 'batch-approach' that Jeff speaks of, or is it less?

    Just trying to set the frames for where 'good enough' might be...

    /Kenneth

    Kenneth

    I am using a newly created email table that has fields for all the parameters necessary for my mail procedure, such as ToAddress, CCAddress,Subjec,Body, and some others,including delivery_dt, and sent_dt. I am trying to build what Sergiy was talking about(or at least what I got out of it) on my 'trigger help' post a few days ago. Basically, i wrote a trigger that inserts all the information, from any table on which I put the trigger, into the email staging table using the function that is the highlight of this thread to set the delivery date. Then, every night I run a procedure to send out the ones where delivery_dt is today, and populate sent_dt. I have another update trigger on the table to delete records from the email staging table that no longer need to be sent. So in short, I will in no way be using this function for large batches, but now that I have it who knows?

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

  • Sergiy (10/22/2007)


    Greg Snidow (10/21/2007)


    Kenneth, I am using it for setting the delivery date for email notification in an email staging table. I am experimenting with a set up suggeted by Sergiy in my post a few days ago called 'trigger help'. Anyhow, I need to be able to set a delivery date of an email based on the enter date of a record. It just so happens this person wanted 5, 10, and 20 day notification using week days only. It just as well could have been 37 weekdays, or 63, or whatever it needs to be.

    So, you need to add not working days but weekdays.

    Why did not you ask for it from the beginning?

    😉

    You know, with 5, 10 and 20 days options it's quite easy:

    5 weekdays = 7 calendar days, 10 = 14, 20 = 28.

    🙂

    If you expect these numbers to be changed you need to follow this (let's take 12 as an example):

    1) convert number of whole weeks to calendar days - 10 wd is 2 whole weeks - 14 days;

    2) Add remainder to the (1) result;

    3) figure out which day of the week you're in.

    This will return 1 for Monday ... 7 for Sunday independently of DATEFIRST settings:

    [font="Courier New"](@@Datefirst + Datepart(dw, YourDate) - 2) %7 +1[/font]

    4) if the remainder (2 in our example) is > 5 - Current Weekday then add 2 days, else leave the number as it is.

    If to translate it into T-SQL it's gonna be something like this:

    [Code]

    SELECT CreateDate + @NotificationDays%5*7 + @NotificationDays/5 +

    CASE WHEN (@@Datefirst + Datepart(dw, CreateDate) - 2) %7 +1 + @NotificationDays/5) > 5

    THEN 2 ELSE 0 END As NotificationDate

    From inserted

    [/Code]

    NB. Don't trust my typing! I don't have a chance to check it now, so do it yourself.

    Sergiy

    I did clarify in the beginning that I should have referred to them as weekdays in the original post. Anyhow, thank you for the explanation of how it works. I can't get my head around what everyone's excellent solutions are doing with the modulo, but I think I am starting to get it.

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

  • Michael Meierruth (10/22/2007)


    How to you select/copy/paste the sample code in the gray background areas?

    When I do this I don't get any line breaks.

    When in the eidtor window - pick the CODE IFCODE, and paste your code between the two tags.

    Still won't paginate it, but that's what puts the gray background in.

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

  • Of course I messed with whole number divisions and remainders.

    Here is correct version (I believe ;))

    [Code]

    SELECT CreateDate + @NotificationDays/5*7 + @NotificationDays%5 +

    CASE WHEN (@@Datefirst + Datepart(dw, CreateDate) - 2) %7 +1 + @NotificationDays%5) > 5

    THEN 2 ELSE 0 END As NotificationDate

    From inserted

    [/Code]

    _____________
    Code for TallyGenerator

  • Matt Miller (10/22/2007)

    When in the eidtor window - pick the CODE IFCODE, and paste your code between the two tags.

    Still won't paginate it, but that's what puts the gray background in.

    Matt,

    I wans't trying to get code into a post but rather out of a post. Jeff's suggestion of going via MS Word works just fine.

  • Sergiy (10/22/2007)


    Of course I messed with whole number divisions and remainders.

    Here is correct version (I believe ;))

    [Code]

    SELECT CreateDate + @NotificationDays/5*7 + @NotificationDays%5 +

    CASE WHEN (@@Datefirst + Datepart(dw, CreateDate) - 2) %7 +1 + @NotificationDays%5) > 5

    THEN 2 ELSE 0 END As NotificationDate

    From inserted

    [/Code]

    Sergiy, I gave it the same try I did with the others, and something seems to be amiss. I should never have @End be a weekend. I am still trying to understand it, just wanted to let you know something's up with it. Thank you for your help.

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

    DROP FUNCTION dbo.fnAddWeekdays_Sergiy

    GO

    CREATE FUNCTION dbo.fnAddWeekdays_Sergiy (@Start DATETIME, @Days INT)

    RETURNS DATETIME

    AS

    BEGIN

    DECLARE @End DATETIME

    SELECT @End = @Start + @Days/5*7 + @Days%5 +

    CASE WHEN (@@DATEFIRST + (DATEPART(dw, @Start) - 2) % 7 + 1 + @Days%5) > 5 THEN 2

    ELSE 0 END

    RETURN @End

    END

    GO

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

    DROP TABLE #Test

    CREATE TABLE #Test

    (

    StartDate SMALLDATETIME

    )

    INSERT INTO #Test

    SELECT '1/1/2008' UNION ALL

    SELECT '1/2/2008' UNION ALL

    SELECT '1/3/2008' UNION ALL

    SELECT '1/4/2008' UNION ALL

    SELECT '1/5/2008' UNION ALL

    SELECT '1/6/2008' UNION ALL

    SELECT '1/7/2008' UNION ALL

    SELECT '1/8/2008' UNION ALL

    SELECT '1/9/2008' UNION ALL

    SELECT '1/10/2008' UNION ALL

    SELECT '1/11/2008' UNION ALL

    SELECT '1/12/2008' UNION ALL

    SELECT '1/13/2008' UNION ALL

    SELECT '1/14/2008' UNION ALL

    SELECT '1/15/2008' UNION ALL

    SELECT '1/16/2008' UNION ALL

    SELECT '1/17/2008' UNION ALL

    SELECT '1/18/2008' UNION ALL

    SELECT '1/19/2008' UNION ALL

    SELECT '1/20/2008' UNION ALL

    SELECT '1/21/2008' UNION ALL

    SELECT '1/22/2008' UNION ALL

    SELECT '1/23/2008' UNION ALL

    SELECT '1/24/2008' UNION ALL

    SELECT '1/25/2008' UNION ALL

    SELECT '1/26/2008' UNION ALL

    SELECT '1/27/2008' UNION ALL

    SELECT '1/28/2008' UNION ALL

    SELECT '1/29/2008' UNION ALL

    SELECT '1/30/2008' UNION ALL

    SELECT '1/31/2008'

    SELECT

    DATENAME(dw,StartDate),

    DATENAME(dw,dbo.fnWorkdays_Sergiy(startdate,5))

    FROM #Test

    DROP TABLE #Test

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

  • I said - I could not test.

    There was a typo in the formula.

    Also I corrected small logical error in the code:

    [Code]

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

    DROP FUNCTION dbo.fnAddWeekdays_Sergiy

    GO

    CREATE FUNCTION dbo.fnAddWeekdays_Sergiy (@Start DATETIME, @Days INT)

    RETURNS DATETIME

    AS

    BEGIN

    DECLARE @End DATETIME

    SELECT @End = @Start + @Days/5*7 + @Days%5 +

    CASE WHEN (@@DATEFIRST + (DATEPART(dw, @Start) - 2)) % 7 + 1 + @Days%5 > 5

    THEN 8 - ((@@DATEFIRST + (DATEPART(dw, @Start) - 2)) % 7 + 1 + @Days%5)

    ELSE 0 END

    RETURN @End

    END

    [/Code]

    Seems all right now.

    _____________
    Code for TallyGenerator

  • Sergiy, wouldn't it be possible to have a small table of only hollidays, and if @DateEnd falls on one of those days just add 1?

    Here are a couple of functions I use. One tells you the number of working (week) days between two dates without using a date tally table and the other lets you add/subtract working days to/from a date and gives you the resultant date.

    The nice thing is that, although commented out because it is not yet implemented, they will work with a table of defined holidays. This is a table of defined holidays that you or your company or your customer will be observing into the foreseeable future (which is as far ahead as you wish to define it). It will also not double-count holidays that happen to occur on a weekend.:cool:

    I haven't tested these on million-row tables but I would be interested in the results if anyone wants to make the effort,:P but these routines were developed more with an eye toward accuracy rather than performance.

    USE [Utility]

    GO

    /****** Object: UserDefinedFunction [dbo].[BizDaysBetween] Script Date: 10/22/2007 18:54:51 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    /*

    =============================================

    Author:

    Tomm Carr

    Create date:

    06/07/2007

    Description:

    Returns the number of week days that occur between two dates.

    Notes:

    * As of the development date above, Business is still debating whether or not to use

    the table of company holidays in calculating the number of work days between two dates.

    Should they decide to use this table, performance will be effected accordingly.

    =============================================

    */

    ALTER FUNCTION [dbo].[BizDaysBetween] (

    @StartDate datetime,

    @EndDate dateTime

    )

    returns int

    as

    begin

    declare @Result int,

    @TmpDate datetime;

    -- We don't want to be picky about whether or not the starting date actually comes before

    -- the end date as passed to this function. So we check here and swap them if necessary.

    if @StartDate > @EndDate

    begin

    set @TmpDate = @StartDate;

    set @StartDate = @EndDate;

    set @EndDate = @TmpDate;

    end--if

    --If starting on a weekend, set to the next business day

    if DatePart( dw, @StartDate ) = 1 -- Sunday

    set @StartDate = @StartDate + 1;

    else if DatePart( dw, @StartDate ) = 7 -- Saturday

    set @StartDate = @StartDate + 2;

    --If ending on a weekend, set to the last business day

    if DatePart( dw, @EndDate ) = 1 -- Sunday

    set @EndDate = @EndDate - 2;

    else if DatePart( dw, @EndDate ) = 7 -- Saturday

    set @EndDate = @EndDate - 1;

    select @Result = DateDiff( d, @StartDate, @EndDate )

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

    -- Uncomment the following section to implement using company holidays in the calculation

    -- - ( SELECT COUNT(*)

    -- FROM dbo.Holiday

    -- WHERE HolidayDate BETWEEN @StartDate AND @EndDate

    -- AND DATEPART(dw,HolidayDate) BETWEEN 2 AND 6

    -- )

    -- If the two dates happen to be the Saturday and Sunday of the same weekend, the result

    -- so far will be -1. As this is not a valid result for this function, turn that value into

    -- a zero. After all, there are no business days between Saturday and Sunday.

    return case when @Result < 0 then 0 else @Result end;

    end--function

    The other:

    USE [Utility]

    GO

    /****** Object: UserDefinedFunction [dbo].[BizDaysAdd] Script Date: 10/22/2007 18:59:55 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    /*

    Created by:

    Tomm Carr

    Date:

    9/25/2007

    Description:

    This function... um... functions much like DateAdd except that it only works with days

    and it adds (or subtracts) *business days* to/from the specified date.

    */

    ALTER FUNCTION [dbo].[BizDaysAdd] (

    @StartDate DateTime,

    @Offset int

    )

    returns DateTime

    as

    begin

    declare @Result Datetime,

    @TmpOffset int;

    -- First, just go ahead and do a rough DateAdd approximation

    set @Result = DateAdd( dd, @Offset + (@Offset * 2 / 7), @StartDate );

    -- Then find the difference between our estimate and the actual count. However,

    -- the calculation is slightly different if the offset is negative.

    if @Offset > 0

    set @TmpOffset = @Offset - dbo.BizDaysBetween( @StartDate, @Result );

    else

    set @TmpOffset = @Offset + dbo.BizDaysBetween( @StartDate, @Result );

    -- Now "walk" to the correct date. The loop is actually pretty fast and will take from

    -- 0 iterations (if we hit the answer the first time -- not likely) up to 4 or 5 for offset

    -- values representing 1000 years or more. Generally the number of iterations for most

    -- values will be 1 or 2.

    while @TmpOffset <> 0

    begin

    set @Result = DateAdd( dd, @TmpOffset + (@TmpOffset * 2 / 7), @Result );

    if @TmpOffset > 0

    set @TmpOffset = @Offset - dbo.BizDaysBetween( @StartDate, @Result );

    else

    set @TmpOffset = @Offset + dbo.BizDaysBetween( @StartDate, @Result );

    end--while

    return @Result;

    end--Function

    Tomm Carr
    --
    Version Normal Form -- http://groups.google.com/group/vrdbms

  • if DatePart( dw, @StartDate ) = 1 -- Sunday

    On my server it means Monday, not Sunday.

    _____________
    Code for TallyGenerator

  • Sergiy (10/22/2007)


    if DatePart( dw, @StartDate ) = 1 -- Sunday

    On my server it means Monday, not Sunday.

    I think that's why Jefff uses the function 'datename' which is always correct independent of 'set datefirst'.

  • try this SP and implement accordingly....

    http://www.codeproject.com/cs/library/datetimelib.asp?print=true

  • Michael Meierruth (10/23/2007)


    Sergiy (10/22/2007)


    if DatePart( dw, @StartDate ) = 1 -- Sunday

    On my server it means Monday, not Sunday.

    I think that's why Jefff uses the function 'datename' which is always correct independent of 'set datefirst'.

    Jeff's function fails if you've got set, say, German language.

    _____________
    Code for TallyGenerator

  • ... or Swedish... or any other non-english language where days are spelled differently 😉

    If you want to know which day of the week a given date is, then there is exactly one way that will always give the correct answer, regardless of DATEFIRST settings, or language settings.

    It's the way Sergiy wrote before:

    -- quote --

    This will return 1 for Monday ... 7 for Sunday independently of DATEFIRST settings:

    (@@Datefirst + Datepart(dw, YourDate) - 2) %7 +1

    -- end quote --

    Just as there is exactly one way to denote datestrings that's also independent of DATEFORMAT or language: 'SSYYMMDD'

    /Kenneth

Viewing 15 posts - 61 through 75 (of 117 total)

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