Solved

calculating 20 working days into future - oracle sql

Posted on 2015-02-18
1
107 Views
Last Modified: 2015-02-20
i need to write a query that outputs the date that is 20 working days in the future from today.

so for example if i ran the query today (18-02-2015) the output would be 18-03-2015.
The query would exclude weekend days and bank holidays.

I have a table called non_working_days which ill need to use somehow! the structure is:

NWD_ID	NWD_DATE	NWD_REASON
J1416	21-Feb-15	WEND
J1417	22-Feb-15	WEND
J1418	28-Feb-15	WEND
J1419	01-Mar-15	WEND
J1420	07-Mar-15	WEND
J1421	08-Mar-15	WEND
J1422	14-Mar-15	WEND
J1423	15-Mar-15	WEND
J1424	21-Mar-15	WEND
J1425	22-Mar-15	WEND
J1426	28-Mar-15	WEND
J1427	29-Mar-15	WEND
J1428	03-Apr-15	BHOL
J1429	04-Apr-15	WEND
J1430	05-Apr-15	WEND
J1431	06-Apr-15	BHOL

etc.

Open in new window


any help to write the query is appreciated
0
Comment
Question by:tonMachine100
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 35

Accepted Solution

by:
johnsone earned 500 total points
ID: 40616496
Try this:
SELECT d 
FROM   (SELECT d, 
               ROWNUM rn 
        FROM   (SELECT d 
                FROM   (SELECT Trunc(SYSDATE) + LEVEL d 
                        FROM   dual 
                        CONNECT BY LEVEL < 45) 
                WHERE  d NOT IN (SELECT nwd_date 
                                 FROM   non_working_days) 
                ORDER  BY d)) 
WHERE  rn = 20; 

Open in new window

0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses

632 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question