Number of working Days (Mon-Fri) between two dates? - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 13 of 13

Thread: Number of working Days (Mon-Fri) between two dates?

  1. #11
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    There is the issue that the two methodologies give different results, also ...
    Code:
    alter session set nls_date_format = 'DD-Mon-YYYY Day';
    
    select
       min_dte,
       max_dte,
       working_days(min_dte,max_dte) wd,
       business_days_between(min_dte,max_dte) bdb
    from
       table_name
    where
       rownum <100 and
       working_days(min_dte,max_dte) != business_days_between(min_dte,max_dte)
    /
    
    MIN_DTE               MAX_DTE                       WD        BDB
    --------------------- --------------------- ---------- ----------
    14-Aug-2002 Wednesday 16-Aug-2002 Friday             2          3
    11-Aug-2002 Sunday    13-Aug-2002 Tuesday            3          2
    08-Aug-2002 Thursday  10-Aug-2002 Saturday           1          2
    04-Aug-2002 Sunday    06-Aug-2002 Tuesday            3          2
    03-Aug-2002 Saturday  05-Aug-2002 Monday             2          1
    01-Aug-2002 Thursday  03-Aug-2002 Saturday           1          2
    31-Jul-2002 Wednesday 02-Aug-2002 Friday             2          3
    27-Jul-2002 Saturday  29-Jul-2002 Monday             2          1
    24-Jul-2002 Wednesday 26-Jul-2002 Friday             2          3
    21-Jul-2002 Sunday    23-Jul-2002 Tuesday            3          2
    18-Jul-2002 Thursday  20-Jul-2002 Saturday           1          2
    
    MIN_DTE               MAX_DTE                       WD        BDB
    --------------------- --------------------- ---------- ----------
    13-Jul-2002 Saturday  15-Jul-2002 Monday             2          1
    11-Jul-2002 Thursday  13-Jul-2002 Saturday           1          2
    10-Jul-2002 Wednesday 12-Jul-2002 Friday             2          3
    07-Jul-2002 Sunday    09-Jul-2002 Tuesday            3          2
    03-Jul-2002 Wednesday 05-Jul-2002 Friday             2          3
    30-Jun-2002 Sunday    02-Jul-2002 Tuesday            3          2
    29-Jun-2002 Saturday  01-Jul-2002 Monday             2          1
    27-Jun-2002 Thursday  29-Jun-2002 Saturday           1          2
    22-Jun-2002 Saturday  24-Jun-2002 Monday             2          1
    19-Jun-2002 Wednesday 21-Jun-2002 Friday             2          3
    16-Jun-2002 Sunday    18-Jun-2002 Tuesday            3          2
    
    MIN_DTE               MAX_DTE                       WD        BDB
    --------------------- --------------------- ---------- ----------
    13-Jun-2002 Thursday  15-Jun-2002 Saturday           1          2
    09-Jun-2002 Sunday    11-Jun-2002 Tuesday            3          2
    08-Jun-2002 Saturday  10-Jun-2002 Monday             2          1
    06-Jun-2002 Thursday  08-Jun-2002 Saturday           1          2
    05-Jun-2002 Wednesday 07-Jun-2002 Friday             2          3
    01-Jun-2002 Saturday  03-Jun-2002 Monday             2          1
    29-May-2002 Wednesday 31-May-2002 Friday             2          3
    26-May-2002 Sunday    28-May-2002 Tuesday            3          2
    23-May-2002 Thursday  25-May-2002 Saturday           1          2
    18-May-2002 Saturday  20-May-2002 Monday             2          1
    16-May-2002 Thursday  18-May-2002 Saturday           1          2
    ...
    It looks like working_days has a bug.

    I note that business_days_between is also inclusive of both min_dte and max_dte, and working_days is not, but that's a diff issue.
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  2. #12
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by slimdave
    [B]There is the issue that the two methodologies give different results, also ...
    Code:
    alter session set nls_date_format = 'DD-Mon-YYYY Day';
    ...
    It looks like working_days has a bug.
    No, not realy. You've got those results because you have different NLS settings than I do. I've specifficaly mentioned that the whole thing depends on the NLS_TERRITORY settings. The numbering of days if the week in America is different than it is in Europe, for example. So if you have NLS_TERRITORY set to "America", you either need to execute another alter session statement to set different territory, e.g.:

    alter session set nls_territory = 'UNITED KINGDOM';

    or you would have to modify the usage of the DECODE(day, ...) inside the function from
    Code:
    ...
      SELECT SUM(DECODE(DAY, '7', 0, '6', 0, 1))
    ...
    to
    Code:
    ...
      SELECT SUM(DECODE(DAY, '7', 0, '1', 0, 1))
    ...
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #13
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Padders :

    Ur PL/SQL block is much rather vv efficient..!!


    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

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