• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 430
  • Last Modified:

calculating numbder of working days from date - oracle 9 sql

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
tonMachine100
Asked:
tonMachine100
  • 2
  • 2
2 Solutions
 
pcelbaCommented:
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
 
sdstuberCommented:
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
 
pcelbaCommented:
Hmm, it seems Oracle is not so bad... :-)
0
 
sdstuberCommented:
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
 
tonMachine100Author Commented:
Thanks both
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now