The following will generate a list showing the current date, and the warning and due dates for the current date.
CREATE TABLE myCal (CAL_Date date,
Bus_Working_Day int)
INSERT INTO myCal
SELECT DATE '2017-06-27', 1 FROM DUAL UNION ALL
SELECT DATE '2017-06-28', 1 FROM DUAL UNION ALL
SELECT DATE '2017-06-29', 1 FROM DUAL UNION ALL
SELECT DATE '2017-06-30', 1 FROM DUAL UNION ALL
SELECT DATE '2017-07-01', 0 FROM DUAL UNION ALL
SELECT DATE '2017-07-02', 0 FROM DUAL UNION ALL
SELECT DATE '2017-07-03', 1 FROM DUAL UNION ALL
SELECT DATE '2017-07-04', 0 FROM DUAL UNION ALL
SELECT DATE '2017-07-05', 1 FROM DUAL UNION ALL
SELECT DATE '2017-07-06', 1 FROM DUAL UNION ALL
SELECT DATE '2017-07-07', 1 FROM DUAL UNION ALL
SELECT DATE '2017-07-08', 0 FROM DUAL UNION ALL
SELECT DATE '2017-07-09', 0 FROM DUAL UNION ALL
SELECT DATE '2017-07-10', 1 FROM DUAL UNION ALL
SELECT DATE '2017-07-11', 1 FROM DUAL UNION ALL
SELECT DATE '2017-07-12', 1 FROM DUAL UNION ALL
SELECT DATE '2017-07-13', 1 FROM DUAL UNION ALL
SELECT DATE '2017-07-14', 1 FROM DUAL UNION ALL
SELECT DATE '2017-07-15', 0 FROM DUAL UNION ALL
SELECT DATE '2017-07-16', 0 FROM DUAL UNION ALL
SELECT DATE '2017-07-17', 1 FROM DUAL UNION ALL
SELECT DATE '2017-07-18', 1 FROM DUAL UNION ALL
SELECT DATE '2017-07-19', 1 FROM DUAL UNION ALL
SELECT DATE '2017-07-20', 1 FROM DUAL UNION ALL
SELECT DATE '2017-07-21', 1 FROM DUAL
;
SELECT cal.CAL_Date
,warn.Warning_Date
,due.Due_Date
FROM myCal cal
LEFT JOIN (SELECT mn.CAL_Date
,ROW_NUMBER() OVER (PARTITION BY mn.CAL_Date ORDER BY alt.CAL_DATE) as rn
,alt.CAL_Date as Warning_Date
FROM myCal mn
INNER JOIN myCal alt ON (alt.CAL_Date > mn.CAL_Date)
WHERE alt.Bus_Working_Day = 1
) warn ON (cal.CAL_Date = warn.CAL_Date AND warn.rn = 7)
LEFT JOIN (SELECT mn.CAL_Date
,ROW_NUMBER() OVER (PARTITION BY mn.CAL_Date ORDER BY alt.CAL_DATE) as rn
,alt.CAL_Date as Due_Date
FROM myCal mn
INNER JOIN myCal alt ON (alt.CAL_Date > mn.CAL_Date)
WHERE alt.Bus_Working_Day = 1
) due ON (cal.CAL_Date = due.CAL_Date AND due.rn = 10)
ORDER BY CAL_Date
;
dbfiddle here
It requires more than a CASE statement, because you need to evaluate whether each following day is a business day or not to find the days you need.
Here's how it works:
- join the table against itself (with mn as the first instance and alt as the second);
- ignore all alt rows where the alt date is less than or equal to the mn date (because the warning and due date will always come after the current date, of course), and where the alt date is not a business working day;
- for each mn date, number the alt dates in ascending order;
- take the calendar table, and outer join it to the results of the above twice (joining to the mn date) as warn and due;
- for the join to warn, take the 7th alt date where the calendar date matches the mn date, for due, the 10th alt date.
You may want to consider (if possible) adding warning_date and due_date to the existing calendar table - you could modify the code above to populate the values, once the base information on the dates is in place. Saves you from joining the calendar table to itself five times to get what you want. (OK, if you have a specific date, then you can hit the warn and due subqueries directly, and don't really have to join back to the calendar table - I did that for demonstration purposes. So, only joining it to itself 4 times).
NOTE: In newer versions of Oracle, where CROSS APPLY and OUTER APPLY are available, you may be able to change these to a more direct (and possibly better performing) query - however, without knowing what version you've got available (and needing to use dbfiddle for my testing, where the version is 11g), I went with this solution.
You should be able to modify this to meet your needs.
UPDATE: You asked what changes would need to be made to use the query in a trigger.
Actually, now that I think about it, I'd be inclined not to use a trigger directly. You see, even if one row is added, all the rows (or, at least, all the rows whose date is, say, within 20 days before any changed date) will need to be updated - which would mean we'd have a trigger that was (technically, at least) recursive.
To avoid that, it might be better to do something somewhat more complicated (of course!).
- Create a new table, let's call it
FLAG_JOB_TO_RUN, and give it one column, JOB_NAME.
- In your trigger,
INSERT the value Update_Warn_Due_Dates into FLAG_JOB_TO_RUN.JOB_NAME (if it doesn't already exist).
Schedule a script (or, even better, create a stored procedure and schedule that) to do the following:
- Check if there is an 'Update_Warn_Due_Dates' entry in
FLAG_JOB_TO_RUN
- If there is:
- Run the
UPDATE version of the SELECT query above to set Warning_Date and Due_Date for all rows in myCal, and
DELETE that row from FLAG_JOB_TO_RUN
Run this job as often as you think is necessary. It might suffice to run it once a night, and to manually run it right after you know there's been a major change; or, you might need to run it every 5 minutes.
