Home » SQL & PL/SQL » SQL & PL/SQL » nth business day
nth business day [message #231694] Wed, 18 April 2007 02:49 Go to next message
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 #231696 is a reply to message #231694] Wed, 18 April 2007 03:20 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
The thread "Counting the number of business days between 2 dates" on AskTom will help you.

Regards
Michel
Re: nth business day [message #231699 is a reply to message #231696] Wed, 18 April 2007 03:35 Go to previous messageGo to next message
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 #231701 is a reply to message #231699] Wed, 18 April 2007 03:40 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
so, rewrite it. It gives you the logic.
Re: nth business day [message #231702 is a reply to message #231699] Wed, 18 April 2007 03:47 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
The problem is the same.
Just make a little effort.

Regards
Michel
Re: nth business day [message #231722 is a reply to message #231702] Wed, 18 April 2007 04:38 Go to previous messageGo to next message
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 #231723 is a reply to message #231722] Wed, 18 April 2007 04:49 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Post what you already done we guide you to the correct avenue.

Regards
Michel
Re: nth business day [message #232027 is a reply to message #231694] Thu, 19 April 2007 02:20 Go to previous messageGo to next message
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 #232044 is a reply to message #232027] Thu, 19 April 2007 03:08 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Work for what?
Moreover there are so many errors in this lone query.
SQL> select count(date) from t 
  2  where to_char(date) not in ('saturday','sunday')
  3    and date between '1-jan-2007' and '12-jan-2007';
select count(date) from t
             *
ERROR at line 1:
ORA-00936: missing expression

SQL> desc t
 Name                          Null?    Type
 ----------------------------- -------- --------------------
 C                                      VARCHAR2(100 CHAR)
 DATE                                   DATE

Regards
Michel
Re: nth business day [message #232052 is a reply to message #232027] Thu, 19 April 2007 03:59 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
oradat wrote on Thu, 19 April 2007 09:20
This query will work ,its guranteed.
That's a very bold statement. Show me a SQL*plus session that confirms this.

MHE
Re: nth business day [message #232098 is a reply to message #232027] Thu, 19 April 2007 07:23 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #233393 is a reply to message #233249] Thu, 26 April 2007 01:04 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member

wow
its only english.................
just take easy.
Re: nth business day [message #233396 is a reply to message #233393] Thu, 26 April 2007 01:06 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
How do you know? Do you know the OP?
Do you know his application?
Re: nth business day [message #233398 is a reply to message #233396] Thu, 26 April 2007 01:08 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
just take easy.

just take it easy.

thanks,
srinivas

[Updated on: Thu, 26 April 2007 01:09]

Report message to a moderator

Re: nth business day [message #233446 is a reply to message #233398] Thu, 26 April 2007 03:02 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I am taking it easy - there wasn't much work involved in breaking your solution.
Re: nth business day [message #233479 is a reply to message #233446] Thu, 26 April 2007 05:22 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #233496 is a reply to message #233484] Thu, 26 April 2007 07:02 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
If somebody asks you to explain your query, simply repeating it will probably not add very much to the clarity of it...
Re: nth business day [message #233497 is a reply to message #233496] Thu, 26 April 2007 07:04 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Laughing
Re: nth business day [message #233499 is a reply to message #233484] Thu, 26 April 2007 07:08 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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
Re: nth business day [message #234225 is a reply to message #233998] Mon, 30 April 2007 09:00 Go to previous message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
Excellent job He Who Must Not Be Named. I haven't used the OLAP commands in Oracle as of yet.
Previous Topic: how to give input such as 100.00 or 23.78 or 80.34
Next Topic: what is the purpose of some commands? and explain them
Goto Forum:
  


Current Time: Tue Feb 11 10:17:46 CST 2025