-
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.
-
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?
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|