Solved

calculating 20 working days into future - oracle sql

Posted on 2015-02-18
1
104 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.‚Äč
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.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

828 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