Home » SQL & PL/SQL » SQL & PL/SQL » nth business day
nth business day [message #231694] |
Wed, 18 April 2007 02:49  |
nirmalnarayan
Messages: 261 Registered: April 2005 Location: India
|
Senior Member |
|
|
How to find out nth business day , from the given date.
For eg. if the given date is 19th April 2007, i want to find out what is the business day after 10 days or 20 days from now .
in this case 2nd May will be the business day after 10 days from 19th April 2007.
(Saturday and sunday are not business days
how to achieve this in a single query statement ?
|
|
|
|
Re: nth business day [message #231699 is a reply to message #231696] |
Wed, 18 April 2007 03:35   |
nirmalnarayan
Messages: 261 Registered: April 2005 Location: India
|
Senior Member |
|
|
My question is to find out the next business day (i.e., Date) from the given date, not to find out the number of business days , between two dates.
|
|
|
|
|
Re: nth business day [message #231722 is a reply to message #231702] |
Wed, 18 April 2007 04:38   |
nirmalnarayan
Messages: 261 Registered: April 2005 Location: India
|
Senior Member |
|
|
Sorry Guys, i am just a small kid in this vast world of SQL , trying to learn something, i have tried out , not getting the correct logic for achieving this. Really appreciate if you can give some idea.
|
|
|
|
Re: nth business day [message #232027 is a reply to message #231694] |
Thu, 19 April 2007 02:20   |
oradat
Messages: 18 Registered: April 2007
|
Junior Member |
|
|
select <count(date)> from <table_name>
where to_char(date)not in ('saturday','sunday') and <date> between '1-jan-2007' and '12-jan-2007';
This query will work ,its guranteed.
|
|
|
|
|
Re: nth business day [message #232098 is a reply to message #232027] |
Thu, 19 April 2007 07:23   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
oradat wrote, quite boldly | select <count(date)> from <table_name>
where to_char(date)not in ('saturday','sunday') and <date> between '1-jan-2007' and '12-jan-2007';
This query will work ,its guranteed.
|
I LOVE guarantees!
SQL> create table no_guarantees
2 ( my_date date);
Table created.
SQL> insert into no_guarantees values (to_date('01-01-2007', 'dd-mm-yyyy'));
1 row created.
SQL> insert into no_guarantees values (to_date('02-01-2007', 'dd-mm-yyyy'));
1 row created.
SQL> insert into no_guarantees values (to_date('03-01-2007', 'dd-mm-yyyy'));
1 row created.
SQL> insert into no_guarantees values (to_date('04-01-2007', 'dd-mm-yyyy'));
1 row created.
SQL> insert into no_guarantees values (to_date('05-01-2007', 'dd-mm-yyyy'));
1 row created.
SQL> insert into no_guarantees values (to_date('06-01-2007', 'dd-mm-yyyy'));
1 row created.
SQL> insert into no_guarantees values (to_date('07-01-2007', 'dd-mm-yyyy'));
1 row created.
SQL> insert into no_guarantees values (to_date('08-01-2007', 'dd-mm-yyyy'));
1 row created.
SQL> insert into no_guarantees values (to_date('09-01-2007', 'dd-mm-yyyy'));
1 row created.
SQL> insert into no_guarantees values (to_date('10-01-2007', 'dd-mm-yyyy'));
1 row created.
SQL> insert into no_guarantees values (to_date('11-01-2007', 'dd-mm-yyyy'));
1 row created.
SQL> insert into no_guarantees values (to_date('12-01-2007', 'dd-mm-yyyy'));
1 row created.
SQL>
SQL> select count(my_date) from no_guarantees
2 where to_char(my_date)not in ('saturday','sunday') and my_date between '1-jan-2007' and '12-jan-2007';
COUNT(MY_DATE)
--------------
12
Oh! No weekends between january 1st and january 12th!
SQL> alter session set nls_date_language = 'ITALIAN';
Session altered.
SQL> select count(my_date) from no_guarantees
2 where to_char(my_date)not in ('saturday','sunday') and my_date between '1-jan-2007' and '12-jan-2007';
where to_char(my_date)not in ('saturday','sunday') and my_date between '1-jan-2007' and '12-jan-2007'
*
ERROR at line 2:
ORA-01843: not a valid month
So much for guarantees...
[Updated on: Thu, 19 April 2007 07:26] Report message to a moderator
|
|
|
Re: nth business day [message #232104 is a reply to message #232027] |
Thu, 19 April 2007 07:54   |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
oradat wrote on Thu, 19 April 2007 03:20 | select <count(date)> from <table_name>
where to_char(date)not in ('saturday','sunday') and <date> between '1-jan-2007' and '12-jan-2007';
This query will work ,its guranteed.
|
That's a crock. In additional to Frank's proof that you're wrong:
FOO SCOTT>l
1 select count(my_date) from no_guarantees
2 where to_char(my_date)not in ('saturday','sunday')
3* and my_date between '1-jan-2007' and '12-jan-2007'
FOO SCOTT>
FOO SCOTT>
FOO SCOTT>/
and my_date between '1-jan-2007' and '12-jan-2007'
*
ERROR at line 3:
ORA-01858: a non-numeric character was found where a numeric was expected
Uh-oh, I guess my NLS_DATE_FORMAT is not that same as yours. Now what?
|
|
|
Re: nth business day [message #232694 is a reply to message #232104] |
Mon, 23 April 2007 00:59   |
pavuluri
Messages: 247 Registered: January 2007
|
Senior Member |
|
|
Hi Nirmalnarayan and and all
plase see this query
this query working.
SELECT TRUNC(TO_DATE('19-04-2007','dd-mm-yyyy'),'ww')+10+6 FROM dual
|
|
|
Re: nth business day [message #232735 is a reply to message #232694] |
Mon, 23 April 2007 02:33   |
pavuluri
Messages: 247 Registered: January 2007
|
Senior Member |
|
|
Hi all,
BEGIN
INSERT INTO no_guarantees VALUES (TO_DATE('01-01-2007', 'dd-mm-yyyy'));
INSERT INTO no_guarantees VALUES (TO_DATE('02-01-2007', 'dd-mm-yyyy'));
INSERT INTO no_guarantees VALUES (TO_DATE('03-01-2007', 'dd-mm-yyyy'));
INSERT INTO no_guarantees VALUES (TO_DATE('04-01-2007', 'dd-mm-yyyy'));
INSERT INTO no_guarantees VALUES (TO_DATE('05-01-2007', 'dd-mm-yyyy'));
INSERT INTO no_guarantees VALUES (TO_DATE('06-01-2007', 'dd-mm-yyyy'));
INSERT INTO no_guarantees VALUES (TO_DATE('07-01-2007', 'dd-mm-yyyy'));
INSERT INTO no_guarantees VALUES (TO_DATE('08-01-2007', 'dd-mm-yyyy'));
INSERT INTO no_guarantees VALUES (TO_DATE('09-01-2007', 'dd-mm-yyyy'));
INSERT INTO no_guarantees VALUES (TO_DATE('10-01-2007', 'dd-mm-yyyy'));
INSERT INTO no_guarantees VALUES (TO_DATE('11-01-2007', 'dd-mm-yyyy'));
INSERT INTO no_guarantees VALUES (TO_DATE('12-01-2007', 'dd-mm-yyyy'));
END;
SELECT my_date,CASE WHEN trim(TO_CHAR( my_date ,'day'))= 'monday' THEN TRUNC( my_date,'ww')+14
WHEN trim(TO_CHAR( my_date ,'day'))= 'tuesday' THEN TRUNC( my_date,'ww')+15
WHEN trim(TO_CHAR( my_date ,'day'))= 'wednesday' THEN TRUNC( my_date,'ww')+16
WHEN trim(TO_CHAR( my_date ,'day'))= 'thursday' THEN TRUNC( my_date,'ww')+17
WHEN trim(TO_CHAR( my_date ,'day'))= 'friday' THEN TRUNC( my_date,'ww')+18
ELSE TRUNC( my_date,'ww')
END businessdays
FROM no_guarantees;
MY_DATE BUSINESSDAYS
1/1/2007 1/15/2007
1/2/2007 1/16/2007
1/3/2007 1/17/2007
1/4/2007 1/18/2007
1/5/2007 1/19/2007
1/6/2007 1/1/2007
1/7/2007 1/1/2007
1/8/2007 1/22/2007
1/9/2007 1/23/2007
1/10/2007 1/24/2007
1/11/2007 1/25/2007
1/12/2007 1/26/2007
|
|
|
Re: nth business day [message #233168 is a reply to message #232694] |
Wed, 25 April 2007 01:48   |
nirmalnarayan
Messages: 261 Registered: April 2005 Location: India
|
Senior Member |
|
|
This is returning 1 day extra than the number of days given.
for example.
-----------------------------------------------------------
the following query should is returning 23-April, why is it not returning 20 April ?
SQL> SELECT TRUNC(TO_DATE('19-04-2007','dd-mm-yyyy'),'ww')+1+6 FROM dual;
23-APR-07
and what is the meaning of using 'ww', what is the logic behind, could you please explain this query if possible ?
when trying to find the business day after 2 business days from date 03-April it is giving 10-April, i think the query is not perfectly working...
SQL> SELECT TRUNC(TO_DATE('03-04-2007','dd-mm-yyyy'),'ww')+2+6 from dual;
10-APR-07
[Updated on: Wed, 25 April 2007 01:53] Report message to a moderator
|
|
|
Re: nth business day [message #233185 is a reply to message #233168] |
Wed, 25 April 2007 02:44   |
pavuluri
Messages: 247 Registered: January 2007
|
Senior Member |
|
|
see this
insert script also there in previous.
SELECT my_date,CASE WHEN trim(TO_CHAR( my_date ,'day'))= 'monday' THEN TRUNC( my_date,'ww')+14
WHEN trim(TO_CHAR( my_date ,'day'))= 'tuesday' THEN TRUNC( my_date,'ww')+15
WHEN trim(TO_CHAR( my_date ,'day'))= 'wednesday' THEN TRUNC( my_date,'ww')+16
WHEN trim(TO_CHAR( my_date ,'day'))= 'thursday' THEN TRUNC( my_date,'ww')+17
WHEN trim(TO_CHAR( my_date ,'day'))= 'friday' THEN TRUNC( my_date,'ww')+18
ELSE TRUNC( my_date,'ww')
END businessdays
FROM no_guarantees;
thanks
srinivas
|
|
|
Re: nth business day [message #233193 is a reply to message #233185] |
Wed, 25 April 2007 03:00   |
nirmalnarayan
Messages: 261 Registered: April 2005 Location: India
|
Senior Member |
|
|
I wanted to know the logic of using 'ww' and +6 in your earlier script..
which is pasted below.
_____________________________________________
Hi Nirmalnarayan and and all
plase see this query
this query working.
SELECT TRUNC(TO_DATE('19-04-2007','dd-mm-yyyy'),'ww')+10+6 FROM dual
____________________________________________
|
|
|
Re: nth business day [message #233194 is a reply to message #233193] |
Wed, 25 April 2007 03:08   |
pavuluri
Messages: 247 Registered: January 2007
|
Senior Member |
|
|
what wrong see this
How to find out nth business day , from the given date.
For eg. if the given date is 19th April 2007, i want to find out what is the business day after 10 days or 20 days from now .
in this case 2nd May will be the business day after 10 days from 19th April 2007.
(Saturday and sunday are not business days
how to achieve this in a single query statement ?
BEGIN
INSERT INTO no_guarantees VALUES (TO_DATE('01-01-2007', 'dd-mm-yyyy'));
INSERT INTO no_guarantees VALUES (TO_DATE('02-01-2007', 'dd-mm-yyyy'));
INSERT INTO no_guarantees VALUES (TO_DATE('03-01-2007', 'dd-mm-yyyy'));
INSERT INTO no_guarantees VALUES (TO_DATE('04-01-2007', 'dd-mm-yyyy'));
INSERT INTO no_guarantees VALUES (TO_DATE('05-01-2007', 'dd-mm-yyyy'));
INSERT INTO no_guarantees VALUES (TO_DATE('06-01-2007', 'dd-mm-yyyy'));
INSERT INTO no_guarantees VALUES (TO_DATE('07-01-2007', 'dd-mm-yyyy'));
INSERT INTO no_guarantees VALUES (TO_DATE('08-01-2007', 'dd-mm-yyyy'));
INSERT INTO no_guarantees VALUES (TO_DATE('09-01-2007', 'dd-mm-yyyy'));
INSERT INTO no_guarantees VALUES (TO_DATE('10-01-2007', 'dd-mm-yyyy'));
INSERT INTO no_guarantees VALUES (TO_DATE('11-01-2007', 'dd-mm-yyyy'));
INSERT INTO no_guarantees VALUES (TO_DATE('12-01-2007', 'dd-mm-yyyy'));
END;
SELECT my_date,CASE WHEN trim(TO_CHAR( my_date ,'day'))= 'monday' THEN TRUNC( my_date,'ww')+14
WHEN trim(TO_CHAR( my_date ,'day'))= 'tuesday' THEN TRUNC( my_date,'ww')+15
WHEN trim(TO_CHAR( my_date ,'day'))= 'wednesday' THEN TRUNC( my_date,'ww')+16
WHEN trim(TO_CHAR( my_date ,'day'))= 'thursday' THEN TRUNC( my_date,'ww')+17
WHEN trim(TO_CHAR( my_date ,'day'))= 'friday' THEN TRUNC( my_date,'ww')+18
ELSE TRUNC( my_date,'ww')
END businessdays
FROM no_guarantees;
MY_DATE BUSINESSDAYS
1/1/2007 1/15/2007
1/2/2007 1/16/2007
1/3/2007 1/17/2007
1/4/2007 1/18/2007
1/5/2007 1/19/2007
1/6/2007 1/1/2007
1/7/2007 1/1/2007
1/8/2007 1/22/2007
1/9/2007 1/23/2007
1/10/2007 1/24/2007
1/11/2007 1/25/2007
1/12/2007 1/26/2007
[Updated on: Wed, 25 April 2007 03:10] Report message to a moderator
|
|
|
Re: nth business day [message #233227 is a reply to message #233194] |
Wed, 25 April 2007 04:36   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
How about this for a problem:SQL> BEGIN
2 INSERT INTO no_guarantees VALUES (TO_DATE('01-01-2007', 'dd-mm-yyyy'));
3 INSERT INTO no_guarantees VALUES (TO_DATE('02-01-2007', 'dd-mm-yyyy'));
4 INSERT INTO no_guarantees VALUES (TO_DATE('03-01-2007', 'dd-mm-yyyy'));
5 INSERT INTO no_guarantees VALUES (TO_DATE('04-01-2007', 'dd-mm-yyyy'));
6 INSERT INTO no_guarantees VALUES (TO_DATE('05-01-2007', 'dd-mm-yyyy'));
7 INSERT INTO no_guarantees VALUES (TO_DATE('06-01-2007', 'dd-mm-yyyy'));
8 INSERT INTO no_guarantees VALUES (TO_DATE('07-01-2007', 'dd-mm-yyyy'));
9 INSERT INTO no_guarantees VALUES (TO_DATE('08-01-2007', 'dd-mm-yyyy'));
10 INSERT INTO no_guarantees VALUES (TO_DATE('09-01-2007', 'dd-mm-yyyy'));
11 INSERT INTO no_guarantees VALUES (TO_DATE('10-01-2007', 'dd-mm-yyyy'));
12 INSERT INTO no_guarantees VALUES (TO_DATE('11-01-2007', 'dd-mm-yyyy'));
13 INSERT INTO no_guarantees VALUES (TO_DATE('12-01-2007', 'dd-mm-yyyy'));
14 END;
15 /
PL/SQL procedure successfully completed.
SQL> alter session set nls_date_language = 'ITALIAN';
Session altered.
SQL> SELECT my_date,CASE WHEN trim(TO_CHAR( my_date ,'day'))= 'monday' THEN TRUNC( my_date,'ww')+14
2 WHEN trim(TO_CHAR( my_date ,'day'))= 'tuesday' THEN TRUNC( my_date,'ww')+15
3 WHEN trim(TO_CHAR( my_date ,'day'))= 'wednesday' THEN TRUNC( my_date,'ww')+16
4 WHEN trim(TO_CHAR( my_date ,'day'))= 'thursday' THEN TRUNC( my_date,'ww')+17
5 WHEN trim(TO_CHAR( my_date ,'day'))= 'friday' THEN TRUNC( my_date,'ww')+18
6 ELSE TRUNC( my_date,'ww')
7 END businessdays
8 FROM no_guarantees;
MY_DATE BUSINESSD
--------- ---------
01-GEN-07 01-GEN-07
02-GEN-07 01-GEN-07
03-GEN-07 01-GEN-07
04-GEN-07 01-GEN-07
05-GEN-07 01-GEN-07
06-GEN-07 01-GEN-07
07-GEN-07 01-GEN-07
08-GEN-07 08-GEN-07
09-GEN-07 08-GEN-07
10-GEN-07 08-GEN-07
11-GEN-07 08-GEN-07
12-GEN-07 08-GEN-07
12 rows selected.
SQL>
|
|
|
Re: nth business day [message #233249 is a reply to message #233227] |
Wed, 25 April 2007 06:34   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
JRowbottom wrote on Wed, 25 April 2007 11:36 |
SQL> alter session set nls_date_language = 'ITALIAN';
|
hehehe, if it wasn't for the Italian...
|
|
|
|
|
|
|
Re: nth business day [message #233479 is a reply to message #233446] |
Thu, 26 April 2007 05:22   |
nirmalnarayan
Messages: 261 Registered: April 2005 Location: India
|
Senior Member |
|
|
OP
I want to do this in single query :
and
Srinivas give this solution
SELECT TRUNC(TO_DATE('19-04-2007','dd-mm-yyyy'),'ww')+10+6 FROM dual
but what is the logic of using 6 and 'ww' here.... and this is not working perfectly also...
when you post something you have to explain your logic, i found no one here explaining things... Masters and Experts.. please explain your solution...so that layman can understand.
|
|
|
Re: nth business day [message #233484 is a reply to message #233479] |
Thu, 26 April 2007 05:54   |
pavuluri
Messages: 247 Registered: January 2007
|
Senior Member |
|
|
SELECT CASE WHEN trim(TO_CHAR(TO_DATE('04-19-2007','mm-dd-yyyy'),'day'))= 'monday'
THEN TRUNC(TO_DATE('19-04-2007','dd-mm-yyyy'),'ww')+13
WHEN trim(TO_CHAR(TO_DATE('04-19-2007','mm-dd-yyyy'),'day'))= 'tuesday'
THEN TRUNC(TO_DATE('19-04-2007','dd-mm-yyyy'),'ww')+14
WHEN trim(TO_CHAR(TO_DATE('04-19-2007','mm-dd-yyyy'),'day'))= 'wednesday'
THEN TRUNC(TO_DATE('19-04-2007','dd-mm-yyyy'),'ww')+15
WHEN trim(TO_CHAR(TO_DATE('04-19-2007','mm-dd-yyyy'),'day'))= 'thursday'
THEN TRUNC(TO_DATE('19-04-2007','dd-mm-yyyy'),'ww')+16
WHEN trim(TO_CHAR(TO_DATE('04-19-2007','mm-dd-yyyy'),'day'))= 'friday'
THEN TRUNC(TO_DATE('19-04-2007','dd-mm-yyyy'),'ww')+17
END businessdays
FROM dual;
dont use
alter session set nls_date_language = 'ITALIAN';
thanks
srinivas
|
|
|
|
|
Re: nth business day [message #233499 is a reply to message #233484] |
Thu, 26 April 2007 07:08   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
pavuluri wrote on Thu, 26 April 2007 12:54 | dont use
alter session set nls_date_language = 'ITALIAN';
|
or 'ARABIC', or 'DUTCH', or 'GERMAN', or 'SPANISH'
and probably not any CHINESE language version
thus ruling out only half of the worlds population..
|
|
|
Re: nth business day [message #233514 is a reply to message #233484] |
Thu, 26 April 2007 07:59   |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
pavuluri wrote on Thu, 26 April 2007 06:54 |
dont use
alter session set nls_date_language = 'ITALIAN';
thanks
srinivas
|
Ok, I guess everyone from Italy is sh** out of luck then.
|
|
|
Re: nth business day [message #233544 is a reply to message #233499] |
Thu, 26 April 2007 09:18   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
We are being a bit harsh on @Pavuluri - a lot of date solutions posted here won't always work if you change the NLS settings.
On the other hand, if someone breaks your code, just put your hand up, say 'Mea Culpa' and fix it.
Here is a single sql statment way of calculating the next working day that should be NLS setting independent:SQL> alter session set nls_territory = "UNITED KINGDOM";
Session altered.
SQL> alter session set nls_date_format ='Dy DD-Mon-yyyy';
Session altered.
SQL> select mod_date
2 ,wk_Start + decode(mod_dy,6,8,7,8,mod_dy)-1 working_day
3 from (select mod_date
4 ,trunc(mod_date,'ww') wk_start
5 ,decode(to_char(MOD_date,'d') + (1-to_char(round(MOD_date,'ww'),'d'))
6 ,0,7
7 ,to_char(MOD_date,'d') + (1-to_char(round(MOD_date,'ww'),'d'))) mod_dy
8 from (select sysdate + level-1 mod_date
9 from dual
10 connect by level <= 20)
11 );
MOD_DATE WORKING_DAY
--------------- ---------------
Thu 26-Apr-2007 Thu 26-Apr-2007
Fri 27-Apr-2007 Fri 27-Apr-2007
Sat 28-Apr-2007 Mon 30-Apr-2007
Sun 29-Apr-2007 Mon 30-Apr-2007
Mon 30-Apr-2007 Mon 30-Apr-2007
Tue 01-May-2007 Tue 01-May-2007
Wed 02-May-2007 Wed 02-May-2007
Thu 03-May-2007 Thu 03-May-2007
Fri 04-May-2007 Fri 04-May-2007
Sat 05-May-2007 Mon 07-May-2007
Sun 06-May-2007 Mon 07-May-2007
Mon 07-May-2007 Mon 07-May-2007
Tue 08-May-2007 Tue 08-May-2007
Wed 09-May-2007 Wed 09-May-2007
Thu 10-May-2007 Thu 10-May-2007
Fri 11-May-2007 Fri 11-May-2007
Sat 12-May-2007 Mon 14-May-2007
Sun 13-May-2007 Mon 14-May-2007
Mon 14-May-2007 Mon 14-May-2007
Tue 15-May-2007 Tue 15-May-2007
20 rows selected.
SQL> alter session set nls_territory = CHINA;
Session altered.
SQL> alter session set nls_date_format ='Dy DD-Mon-yyyy';
Session altered.
SQL>
SQL> select mod_date
2 ,wk_Start + decode(mod_dy,6,8,7,8,mod_dy)-1 working_day
3 from (select mod_date
4 ,trunc(mod_date,'ww') wk_start
5 ,decode(to_char(MOD_date,'d') + (1-to_char(round(MOD_date,'ww'),'d'))
6 ,0,7
7 ,to_char(MOD_date,'d') + (1-to_char(round(MOD_date,'ww'),'d'))) mod_dy
8 from (select sysdate + level-1 mod_date
9 from dual
10 connect by level <= 20)
11 );
MOD_DATE WORKING_DAY
--------------- ---------------
Thu 26-Apr-2007 Thu 26-Apr-2007
Fri 27-Apr-2007 Fri 27-Apr-2007
Sat 28-Apr-2007 Mon 30-Apr-2007
Sun 29-Apr-2007 Mon 30-Apr-2007
Mon 30-Apr-2007 Mon 30-Apr-2007
Tue 01-May-2007 Tue 01-May-2007
Wed 02-May-2007 Wed 02-May-2007
Thu 03-May-2007 Thu 03-May-2007
Fri 04-May-2007 Fri 04-May-2007
Sat 05-May-2007 Mon 07-May-2007
Sun 06-May-2007 Mon 07-May-2007
Mon 07-May-2007 Mon 07-May-2007
Tue 08-May-2007 Tue 08-May-2007
Wed 09-May-2007 Wed 09-May-2007
Thu 10-May-2007 Thu 10-May-2007
Fri 11-May-2007 Fri 11-May-2007
Sat 12-May-2007 Mon 14-May-2007
Sun 13-May-2007 Mon 14-May-2007
Mon 14-May-2007 Mon 14-May-2007
Tue 15-May-2007 Tue 15-May-2007
20 rows selected.
SQL> alter session set nls_language = ITALIAN;
Session altered.
SQL>
SQL> alter session set nls_date_format ='Dy DD-Mon-yyyy';
Session altered.
SQL>
SQL> select mod_date
2 ,wk_Start + decode(mod_dy,6,8,7,8,mod_dy)-1 working_day
3 from (select mod_date
4 ,trunc(mod_date,'ww') wk_start
5 ,decode(to_char(MOD_date,'d') + (1-to_char(round(MOD_date,'ww'),'d'))
6 ,0,7
7 ,to_char(MOD_date,'d') + (1-to_char(round(MOD_date,'ww'),'d'))) mod_dy
8 from (select sysdate + level-1 mod_date
9 from dual
10 connect by level <= 20)
11 );
MOD_DATE WORKING_DAY
--------------- ---------------
Gio 26-Apr-2007 Gio 26-Apr-2007
Ven 27-Apr-2007 Ven 27-Apr-2007
Sab 28-Apr-2007 Lun 30-Apr-2007
Dom 29-Apr-2007 Lun 30-Apr-2007
Lun 30-Apr-2007 Lun 30-Apr-2007
Mar 01-Mag-2007 Mar 01-Mag-2007
Mer 02-Mag-2007 Mer 02-Mag-2007
Gio 03-Mag-2007 Gio 03-Mag-2007
Ven 04-Mag-2007 Ven 04-Mag-2007
Sab 05-Mag-2007 Lun 07-Mag-2007
Dom 06-Mag-2007 Lun 07-Mag-2007
Lun 07-Mag-2007 Lun 07-Mag-2007
Mar 08-Mag-2007 Mar 08-Mag-2007
Mer 09-Mag-2007 Mer 09-Mag-2007
Gio 10-Mag-2007 Gio 10-Mag-2007
Ven 11-Mag-2007 Ven 11-Mag-2007
Sab 12-Mag-2007 Lun 14-Mag-2007
Dom 13-Mag-2007 Lun 14-Mag-2007
Lun 14-Mag-2007 Lun 14-Mag-2007
Mar 15-Mag-2007 Mar 15-Mag-2007
The guts of this solution are this:decode(to_char(MOD_date,'d') + (1-to_char(round(MOD_date,'ww'),'d'))
,0,7
,to_char(MOD_date,'d') + (1-to_char(round(MOD_date,'ww'),'d')))
This takes advantage of the fact that round(date,'ww') will always take you to the MONDAY of that week. This lets you find out what the value of to_char(date,'d') for that Monday, which lets you take the NLS settings out of the equation.
|
|
|
Re: nth business day [message #233557 is a reply to message #233544] |
Thu, 26 April 2007 10:45   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
But that is not what (I think) the OP asked for.
He asked how to get the n-th business day from today.
So, for the 3rd business-day starting at your Thursday, April 26th, it should return Tuesday, May 1st. Not this whole set of 'today's next businessday' rows.
And that is (partly) why I was on to pavuluri. He came up with 5 different queries, without any explanation, that all returned the wrong result.
I THINK (no guarantees) that this query will do that, independant of any nls setting.
select the_date
from (select the_date
, row_number() over (order by the_date) the_row
from (select to_date('&startdate_in_your_dateformat') + rownum as the_date
from dual
connect by level < (&&nth_business_day * (7/5) + 1)
)
where to_char(the_date, 'DAY') not in (to_char(to_date('28-04-2007', 'dd-mm-yyyy'), 'DAY'), to_char(to_date('29-04-2007', 'dd-mm-yyyy'), 'DAY'))
)
where the_row = &&nth_business_day
;
This query uses the fact that I know that april 28 and 29 are a saturday and sunday.
[Edit: added query. You are right in the fact that it's easy boo-ing from the side. If you BOO, you should also show something that is (in my view) the right solution]
[Updated on: Thu, 26 April 2007 10:57] Report message to a moderator
|
|
|
Re: nth business day [message #233580 is a reply to message #233544] |
Thu, 26 April 2007 13:17   |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
JRowbottom wrote on Thu, 26 April 2007 10:18 | We are being a bit harsh on @Pavuluri - a lot of date solutions posted here won't always work if you change the NLS settings.
|
I agree for the most part, but based on some of the really poor answers (eg. incorrect) by Pavuluri and bad advice to newbies (eg. using character strings for DATEs) and hijacking other peoples posts asking for the queries, I'd like to see one post by him that is clear and correct.
|
|
|
Re: nth business day [message #233711 is a reply to message #233580] |
Fri, 27 April 2007 03:54   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Frank - Hmmm....I'd not read the OP like that, but I see where you're coming from. Hopefully one of us has provided an answer he can use.
Joy_Division - I quite agree.
|
|
|
Re: nth business day [message #233998 is a reply to message #231694] |
Sat, 28 April 2007 16:37   |
Volder
Messages: 38 Registered: April 2007 Location: Russia
|
Member |
|
|
If you're looking for N-th working day after the given date (when we say "working" meaning mon-fri) you can use MODEL in 10g.
Here is an NLS-independent query, covering your requirments:
SQL> with t as (select TO_DATE('26-04-2007', 'dd-mm-yyyy') dt from dual union all
2 select TO_DATE('27-04-2007', 'dd-mm-yyyy') from dual union all
3 select TO_DATE('28-04-2007', 'dd-mm-yyyy') from dual union all
4 select TO_DATE('29-04-2007', 'dd-mm-yyyy') from dual union all
5 select TO_DATE('30-04-2007', 'dd-mm-yyyy') from dual union all
6 select TO_DATE('01-05-2007', 'dd-mm-yyyy') from dual union all
7 select TO_DATE('02-05-2007', 'dd-mm-yyyy') from dual union all
8 select TO_DATE('03-05-2007', 'dd-mm-yyyy') from dual union all
9 select TO_DATE('04-05-2007', 'dd-mm-yyyy') from dual union all
10 select TO_DATE('05-05-2007', 'dd-mm-yyyy') from dual union all
11 select TO_DATE('06-05-2007', 'dd-mm-yyyy') from dual union all
12 select TO_DATE('07-05-2007', 'dd-mm-yyyy') from dual)
13 --end of test data
14 select dt,work_dt from t
15 model
16 partition by (dt)
17 dimension by (0 dim)
18 measures (dt work_dt, 0 num)
19 rules iterate(1000) until (num[0] =10)--we are looking for 10th working day after dt
20 (work_dt[0]=work_dt[0]+1,
21 num[0] = num[0]+decode(mod(to_char(work_dt[0],'j'),7),5,0,6,0,1))
22 order by 1
23 /
DT WORK_DT
----------- -----------
26.04.2007 10.05.2007
27.04.2007 11.05.2007
28.04.2007 11.05.2007
29.04.2007 11.05.2007
30.04.2007 14.05.2007
01.05.2007 15.05.2007
02.05.2007 16.05.2007
03.05.2007 17.05.2007
04.05.2007 18.05.2007
05.05.2007 18.05.2007
06.05.2007 18.05.2007
07.05.2007 21.05.2007
12 rows selected
|
|
|
|
Goto Forum:
Current Time: Tue Feb 11 10:17:46 CST 2025
|