Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

calculating 20 working days into future - oracle sql

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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to take different types of Oracle backups using RMAN.

715 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