Solved

calculating numbder of working days from date - oracle 9 sql

Posted on 2010-08-31
5
422 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
[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
5 Comments
 
LVL 42

Accepted Solution

by:
pcelba earned 125 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

Open in new window

0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 125 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

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

Expert Comment

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

by:tonMachine100
ID: 33575661
Thanks both
0

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
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 with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

728 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