Report DATE, X Working Days from Input Date
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Report DATE, X Working Days from Input Date

  1. #1
    Join Date
    Nov 2001
    Location
    Sheffield, England, UK
    Posts
    78

    Report DATE, X Working Days from Input Date

    Hi,

    I'm just starting to try and get my head around this one for someone who wants the resultant code implemented like 'yesterday'.

    We have a table custom populated with all the dates in the year that are not working days. Consquently we can't just discount Saturdays and Sundays as non working days. We also have custom Public and Business holidays included.

    From a given input date and number of working days into the future, the users want a function that will return the actual working day date - referencing their custom list of non working days.

    Anyone any ideas how to best formulate the resultant function for best performance (over neatness!)?

    The chances are users will be using the function to perform query results for an input set of hundreds of thousands of rows.

    - Tony.

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    I'd think about generating a two column table (probably an IOT) with the date and the number-of-working-days-since-some-arbitrary-origin. You would always get your answer with two look-up's in this table, whereas if you used the original table you'd have to look-up every date in the interval.

    In physical terms, how about evaluating: work-day(date) from the db table, date(work-day) from an indexed array in memory?

    The hard part of this problem is keeping the table(s) up to date far enough into the future - I've had cases where banks decided at short notice to close for an extra day around Chistmas/New-Year.

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    It seems to me that a list of non-working days is doing you no favours here -- what you really need is a list of working days (you could do this dynamically of course by minus'ing your non-working days from a list of all days, but it wouldn't perform as well of course).

    Anyway, if you had such a list then you could do the following ...

    Code:
    create table date_work
    	(
    	date_of_day	date primary key
    	)
    /
    
    
    Select lead_date
    From
       (
       Select date_of_day,
       	  lead(date_of_day,10) Over (Order By date_of_day) lead_date
       From   date_work
       where  date_of_day >= '01-mar-2004'
       )
    Where date_of_day = '01-mar-2004'
    /
    So this would find the date in the table that is 10 rows after the 01-mar-2004, when ordered by date
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    6 years ago I had the same requirement from one of the State Govt Depts in US.
    First I created table that stores the all non-working days, this includes Holidays, Saturdays and Sundays. User is allowed to add future holidays or to change Sat/Sun as working day.

    For a given date and the number of working days I wrote a function that will return the future working day.
    This is not very difficult.

    But you said, "Consquently we can't just discount Saturdays and Sundays as non working days". This shows the requirement is not very clear.

    Tamil

  5. #5
    Join Date
    Nov 2001
    Location
    Sheffield, England, UK
    Posts
    78
    Thanks very much for the responses.

    The users further complicated the issue by stating that they wanted the function to also give an offset date (negative) to the input date.

    What I've done is (as per Slimdave) [many thanks], simplified the issue be populating a table full of working days.

    I've also included a column of 'day_sequence', and ordered this from min date to max date.

    Now, the function will derive the day_sequence for a given input date, add the (+ve or -ve) offset number of days, and then re-lookup the given working day for the sequence.

    The quickest solution I thought up on the spur of the moment, but obviously involves a couple of table scans (albeit very small table).

    Anyone an idea on refining my current end product?

    :-)

    T.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width