| get 7 working day excluding holidays in Oracle [message #645293] |
Wed, 02 December 2015 07:42  |
manoj12
Messages: 210
Registered: March 2008
Location: India
|
Senior Member |
|
|
Hi Sir,
I wanted to get the date of 7th working day of a month excluding holidays(it also excludes saturday and sunday) and in Oracle.
for e.g Lets take an example of Dec-2015 month.The 7th working date of a month of december 2015 excluding saturday and sunday is 9-12-2015.It should return me this date.
Please appreciate your help?
Regards,
[Updated on: Wed, 02 December 2015 07:44] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: get 7 working day excluding holidays in Oracle [message #645304 is a reply to message #645293] |
Wed, 02 December 2015 09:29  |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
Don't mind, but, on a serious note, you have been with OraFAQ since 6+ years, and you posted such a low quality question.
Neither did you provide the required code as a working test case, nor did you clearly show your desired output.
Also, it is a FAQ. Did you at least search the web before posting? No problem, if you couldn't get anywhere near, you could show us what have you tried so far.
If you don't want to be treated as a newbie here after 6+ years, then please make some effort. It would help you, not me.
Quote:The 7th working date of a month of december 2015 excluding saturday and sunday is 9-12-2015
Why? What's the rule? As asked by others already, what do you mean by a working day? I work everyday, and my calendar has 7 working days a week just like any other day of the week 
[Updated on: Wed, 02 December 2015 09:46] Report message to a moderator |
|
|
|
| Re: get 7 working day excluding holidays in Oracle [message #645306 is a reply to message #645293] |
Wed, 02 December 2015 09:44 |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
So, what have you come up with so far, Manoj?
It should be clear to you that you need a table of non-work days, populated with your holidays and weekends.
Then you could (just one solution) create a table with all the days and use a compound query (think MINUS) to extract the working days. Then use a query against that with a predicate to extract the month you want, and another query against that with ha ROWNUM predicate to get the seventh working day.
You can do it all in one statement with a couple of in-line views, or using a WITH common table expression clause.
|
|
|
|
| Re: get 7 working day excluding holidays in Oracle [message #645308 is a reply to message #645299] |
Wed, 02 December 2015 11:17 |
 |
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
If your holidays are just week-end days then you can use:
SQL> select to_char(dt,'Dy DD-MON-YYYY', 'NLS_DATE_LANGUAGE=AMERICAN') dt
2 from ( select trunc(sysdate,'MONTH')+level- 1 dt,
3 row_number() over (order by level) rn
4 from dual
5 where to_char(trunc(sysdate,'MONTH')+level- 1,'Dy','NLS_DATE_LANGUAGE=AMERICAN')
6 not in ('Sat','Sun')
7 connect by level <= 11 )
8 where rn = 7
9 /
DT
---------------
Wed 09-DEC-2015
If you have other holidays, then you have to add a new table with all these holidays as others mentioned.
The exclusion can be made in the same way than in my query, just changing what is between () in line 6 (and maybe the 11 in line 7, I let you find the general way to write it).
[Updated on: Wed, 02 December 2015 11:18] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
