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

[slimdave]

There is the issue that the two methodologies give different results, also ...

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.

[jmodic]

Originally posted by slimdave
[B]There is the issue that the two methodologies give different results, also ...

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

...
  SELECT SUM(DECODE(DAY, '7', 0, '6', 0, 1))
...

to

...
  SELECT SUM(DECODE(DAY, '7', 0, '1', 0, 1))
...

[abhaysk]

Padders :

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


Abhay.