Post reply

Auto-roll date

  • October 5, 2005 at 9:54 pm

    #90204

    I would like to take an action to move forward a specific date in a SQL table to the 'next business date' based on two conditions: First, is date n+1 a weekday (i.e., Datepart(dw,(date n + 1) between 2 and 6; and second, is date n+1 included in the Holiday_Date field of a table used to store company holidays.

    I've messed around with this for some hours, but I can't get seem to get the WHILE statements to work as I'd like.  Here' is my pseudo-code:

    1.  Use variables @OldDate, @NewDate, @LoopParm

    2.  Set @NewDate = @OldDate + 1

         Set @LoopParm = 0

    3.  WHILE @LoopParm = 0

    4.  Check to see if @NewDate is a Weekday

    5a.  IF 'No' SET @NewDate = @NewDate + 1 (try the next day) and go back to Step 4

    5b.  IF 'Yes' THEN Check to see if @NewDate is in the Holiday_Date list

    6a.  IF 'Yes' THEN SET @NewDate = @NewDate + 1 (try the next day) and go back to Step 4

    6b.  IF 'No' (then I've satisfied both requirements that this IS a Weekday and it is NOT a Holiday), then Set @LoopParm = 1 and EXIT Loop

    7.  Use @NewDate to update other tables as required.

    Any help would be appreciated.  Thanks.

     


    Richard D. Cushing
    Sr. Consultant
    SYNGERGISTIC SOFTWARE SOLUTIONS, LLC

  • October 5, 2005 at 10:39 pm

    #595331

    I'm seeing frequent use of the word "loop" in your pseudo code. Hopefully this doesn't translate into a cursor

    Here's how you can move a date forward skipping over weekends.

    SET NOCOUNT ON
SET DATEFORMAT dmy
SET DATEFIRST 1
    DECLARE @OldDate datetime
    SET @OldDate = '30 Sep 2005' -- Friday
SELECT @OldDate as [OldDate], 
    CASE 
        WHEN DATEPART(dw, @OldDate) <= 4 THEN DATEADD(dd, 1, @OldDate)
        ELSE DATEADD(dd, ABS(DATEPART(dw, @OldDate)-7)+1, @OldDate)
    END as [NewDate]

    Presumably you can expand on the CASE statement to take your Holiday date into consideration before accounting for weekends.

     

    --------------------
    Colt 45 - the original point and click interface

  • October 6, 2005 at 5:22 pm

    #595728

    Thanks for the help.

    No, I wasn't thinking about a cursor. I was, however, thinking about a WHILE... BEGIN statement to move the date forward one day at a time until both conditions were met.

    Your offering is certainly more elegant.  Thanks again.


    Richard D. Cushing
    Sr. Consultant
    SYNGERGISTIC SOFTWARE SOLUTIONS, LLC

  • October 6, 2005 at 8:29 pm

    #595746

    Your solution works great to meet the first criteria (regarding day-of-week), but when I try to use the following construct to check for the holiday schedule, I fail to get the results I would expect:

    CASE

    WHEN @OldDate IN (SELECT Holiday_Date FROM tciHoliday_List) THEN SET @OldDate = @OldDate + 1 -- to skip holiday date

    END

    Does the 'IN' construct not work when finding DateTime items?


    Richard D. Cushing
    Sr. Consultant
    SYNGERGISTIC SOFTWARE SOLUTIONS, LLC

  • October 6, 2005 at 8:50 pm

    #595749

    No you can't use the IN like this.

    Without seeing the whole query and tables involved, here is how I might tackle the problem.

    Left join from the table you're updating to the holiday list on the date. In a CASE statement if the holiday date is null use the old date, otherwise add a day.

    Here's some test code for you.

    SET NOCOUNT ON
SET DATEFORMAT dmy
SET DATEFIRST 1
    CREATE TABLE #tbl (
    old_date datetime
)
    CREATE TABLE #Hol (
    holiday_date datetime
)
    -- populate date data
INSERT INTO #tbl VALUES ('26 Sep 2005')
INSERT INTO #tbl VALUES ('27 Sep 2005')
INSERT INTO #tbl VALUES ('28 Sep 2005')
INSERT INTO #tbl VALUES ('29 Sep 2005')
INSERT INTO #tbl VALUES ('30 Sep 2005')
INSERT INTO #tbl VALUES ('01 Oct 2005')
INSERT INTO #tbl VALUES ('02 Oct 2005')
INSERT INTO #tbl VALUES ('03 Oct 2005')
INSERT INTO #tbl VALUES ('04 Oct 2005')
INSERT INTO #tbl VALUES ('05 Oct 2005')
INSERT INTO #tbl VALUES ('06 Oct 2005')
INSERT INTO #tbl VALUES ('07 Oct 2005')
    -- populate holiday data
INSERT INTO #Hol VALUES ('28 Sep 2005')
INSERT INTO #Hol VALUES ('03 Oct 2005')
INSERT INTO #Hol VALUES ('07 Oct 2005')
    -- check original data values
SELECT old_date, DATENAME(dw, old_date) FROM #tbl
SELECT holiday_date, DATENAME(dw, holiday_date) FROM #Hol
    -- update to skip holiday date
UPDATE #tbl
SET old_date = 
    CASE 
        WHEN #hol.holiday_date IS NULL THEN old_date 
        ELSE DATEADD(dd, 1, old_date)
    END
FROM #tbl
    LEFT JOIN #hol
    ON #tbl.old_date = #hol.holiday_date
    -- check result
SELECT old_date, DATENAME(dw, old_date) FROM #tbl
    -- update to skip weekend
UPDATE #tbl
SET old_date = 
    CASE
        WHEN DATEPART(dw, old_date) <= 4 THEN DATEADD(dd, 1, old_date)
        ELSE DATEADD(dd, ABS(DATEPART(dw, old_date)-7)+1, old_date)
    END 
    -- check result
SELECT old_date, DATENAME(dw, old_date) FROM #tbl
    DROP TABLE #tbl
DROP TABLE #Hol

     

     

    --------------------
    Colt 45 - the original point and click interface

  • October 7, 2005 at 8:20 am

    #595879

    This is something that would be much easier to solve using a calendar table. Just fill a Tables with dates from 200? to 2020+ with a date column and and IsBusinessDay column. Then flag the dates that are holidays or weekends to false. Then you can simply select from that table to get the next nth business day. This will run much faster as a set based solution.

  • October 7, 2005 at 8:21 am

    #595880

    And what about this? Using standard format for datetime (YYYYMMDD); taking into account how the question was formulated("datepart between 2 and 6"), I decided to use a SET DATEFIRST 7 setting.

    create table company_holidays(holiday_date datetime)

    insert into company_holidays values ('20051007')

    SET DATEFIRST 7

    DECLARE @olddate datetime

    DECLARE @newdate datetime

    SET @olddate = '20051006 15:28'

    SET @oldDate = DATEADD(d, DATEDIFF(d, 0, @oldDate), 0)/*cut away time portion*/

    SET @newdate = @olddate + 1

    WHILE DATEPART(dw,@newdate) IN (7,1) OR @newdate IN (select Holiday_date from company_holidays)

    BEGIN

    SET @newdate = @newdate + 1

    END

    SELECT @newdate

    Result is 2005-10-10 00:00:00.000 - skipped Friday(company holiday), Saturday and Sunday. Should be fine... just don't forget cutting away the time portion... I don't suppose it is in the holidays table, but it could appear in @oldDate and then it would never equal to the dates from hliday table.

    HTH, Vladan

    BTW, I agree with Remi that best way would be to use auxiliary Dates table and go with the set-based solution. However, if it something that is calculated for a few hundred rows daily, then the difference isn't so big and my solution could be easier to implement. That's up to you...

  • October 8, 2005 at 2:01 am

    #596145

    Hmmm ... I thought my solution was set based and it didn't require the generation of an extra table.

    Was I wrong ???

     

    --------------------
    Colt 45 - the original point and click interface

  • October 8, 2005 at 6:49 am

    #596148

    Maybe I misread it... gonna recheck it Thuesday.

  • October 9, 2005 at 7:33 pm

    #596187

    Thanks for all your help.  I'll try to make one of these options work in my context.


    Richard D. Cushing
    Sr. Consultant
    SYNGERGISTIC SOFTWARE SOLUTIONS, LLC

  • October 10, 2005 at 1:14 am

    #596209

    phillcart,

    your solution is set-based, but it has one serious flaw... it doesn't work as expected (or at least not as I understood the question). You check holidays table first, and then weekends. In fact, both should be checked at once. Look at your results. For example 27.09.; in first check it finds out, that the 28th is a company holiday, so the day is not added and date remains 28th. In second step, your code finds that 28th is not on a weekend, so it adds one and your result is 28th - which is incorrect, because it is a holiday. Then there are three results with the date of 03.10., which is a holiday again (but a holiday after weekend this time). Generally, it will only work when there are no holidays around... or have I missed something?

  • October 10, 2005 at 1:27 am

    #596210

    Ahh .. yes .. well spotted. I'd prefer to have a single update myself, but wouldn't it work out if the weekend was checked first and then the update ( without a case and an inner join instead of a left join ) to advance the holiday dates?

    Hmmm ... actually looking at this more logically on a Monday, you'd still have to run this multiple times. What happens if you have two holiday dates together ?? Maybe the auxillary table does have its uses ...

     

    --------------------
    Colt 45 - the original point and click interface

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

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