Solved

calculating 20 working days into future - oracle sql

Posted on 2015-02-18
1
89 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
1 Comment
 
LVL 34

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
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.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

705 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now