Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

calculating 20 working days into future - oracle sql

Posted on 2015-02-18
1
Medium Priority
?
111 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 35

Accepted Solution

by:
johnsone earned 2000 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

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

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.​
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

971 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