Solved

# calculating numbder of working days from date - oracle 9 sql

Posted on 2010-08-31
Medium Priority
425 Views
I need to calculate the expected delivery date for a series of orders. The expected delivery date is 7 working days from the order date. i have use of a table which contains all non working days.

ive attached some sample data:
- table orders - order_dte is the order date and expected_del_dte is the field that i need to calculate
- table non_working_days - is a list of non working days - this includes weekend days and bank holidays (this sample just includes non working days in August)

is it possible to use the non working days table to calculate 7 working days from the order date? any help is appreciated
SAMPLE-DATA.xls
0
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
• 2
• 2

LVL 42

Accepted Solution

pcelba earned 500 total points
ID: 33570405
This query should work on SQL Server, Oracle is similar. You will just need one more table containing all days.

Or you may mark non-working days in the alldays table.

Or you may create alldays table as temporary table when you need to calculate number of days.
``````declare @wkd integer
SET @wkd = 7

SELECT MAX(day_dte) AS deliv_date
FROM (
SELECT TOP @wkd a.day_dte
FROM all_days a
INNER JOIN orders o ON w.wd_dte >= o.order_dte
WHERE a.day_dte NOT IN (SELECT nwd_dte FROM non_working_days)
ORDER BY a.day_dte
) wkd

-- day_non_wkd column in all_days table:

SELECT MAX(day_dte) AS deliv_date
FROM (
SELECT TOP @wkd a.day_dte
FROM all_days a
INNER JOIN orders o ON w.wd_dte >= o.order_dte
WHERE a.day_non_wkd = 0
ORDER BY a.day_dte
) wkd
``````
0

LVL 74

Assisted Solution

sdstuber earned 500 total points
ID: 33570961
your sample data and expected results don't match

Aug1 and Aug2 are weekend by your data , so 7 working days from Aug 1 (order id 1) is Aug 11 not Aug 10.

try this....
Change the 20 to be some number larger than the 7+longest set of contiguous non-working days
So, if you could have a 5 day weekend including holidays then change it to 13 (or just leave it as 20)

SELECT order_id, order_dte, expected_dte
FROM (SELECT order_id,
order_dte,
order_dte + n expected_dte,
ROW_NUMBER() OVER (PARTITION BY order_id, order_dte ORDER BY n) rn
FROM orders,
(SELECT LEVEL n
FROM DUAL
CONNECT BY LEVEL < 20)
WHERE NOT EXISTS
(SELECT NULL
FROM non_working_days
WHERE order_dte + n = nwd_dte))
WHERE rn = 7

0

LVL 42

Expert Comment

ID: 33571285
Hmm, it seems Oracle is not so bad... :-)
0

LVL 74

Expert Comment

ID: 33571354
you could do something similar ito my query n sql server with a CTE to generate your next 20 days rather than creating an "all days" table and in SS2005 and higher you can use the row_number analytic

so, sql server is not so bad either.  but I like Oracle better.  :)
0

Author Closing Comment

ID: 33575661
Thanks both
0

## Featured Post

Question has a verified solution.

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

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
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 explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
###### Suggested Courses
Course of the Month13 days, 1 hour left to enroll

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

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