• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 428
  • 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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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