Solved

calculating numbder of working days from date - oracle 9 sql

Posted on 2010-08-31
5
409 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 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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
ORA-02288: invalid OPEN mode 2 59
Need help with a Stored Proc on Sql Server 2012 4 28
Sum particular field in database 2 16
PL/SQL: ORA-00979: not a GROUP BY expression 3 32
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.
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.‚Äč
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to recover a database from a user managed backup

830 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