Using Oracle XE, how to create a CASE statement To return a specific date

casedateoracle-xe

I am Trying to create a view based on a business calendar table called Business Payment Calendar view with two new calculated columns:

  1. Payment Warning Date (Payment_Warning_Date)
  2. Payment Due Date (Payment_Due_Date)

And, also the non-calculated Transaction_Date

Those dates are based on the number of BUSINESS days from the transaction date from the business calendar, 7 and 10 business days. The Business Calendar table contains columns populated for the year.

CAL_Date (which is our datestamp),
Day_Name,
Month_Name,
Cal_Year,
Month_Num,
Weekend (True/False),
Holiday (True/False),
Bus_Working_Day (True/False).

So for example, say you have a purchase today, the output will be:

Transaction_Date: 6/26/2017 
Payment Warning Date: 7/6/2017
Payment Due Date: 7/11/2017

Figure we have the weekend coming up, next Monday (July 3) is a work day, but Tuesday (July 4) is a US holiday, then 3 more work days.

I'm just not having any luck building a case statement that properly takes into account the Bus_Working_Day field for SYSDATE+7 and +10 and then return the correct date.

Best Answer

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!).

  1. Create a new table, let's call it FLAG_JOB_TO_RUN, and give it one column, JOB_NAME.
  2. In your trigger, INSERT the value Update_Warn_Due_Dates into FLAG_JOB_TO_RUN.JOB_NAME (if it doesn't already exist).

  3. 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.

Related Question