Solved

calculating numbder of working days from date - oracle 9 sql

Posted on 2010-08-31
5
380 Views
Last Modified: 2012-05-10
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
Comment
Question by:tonMachine100
  • 2
  • 2
5 Comments
 
LVL 41

Accepted Solution

by:
pcelba earned 125 total points
Comment Utility
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

Open in new window

0
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 125 total points
Comment Utility
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 41

Expert Comment

by:pcelba
Comment Utility
Hmm, it seems Oracle is not so bad... :-)
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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

by:tonMachine100
Comment Utility
Thanks both
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
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 explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

763 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

7 Experts available now in Live!

Get 1:1 Help Now