2

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.

2

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.

  • First off, thank you very much for the detailed reply and the possible answer, I'm going to try this out when I get home and comment back on experience. Second, holy-crap that's much more difficult than what I thought it was. I thought I was getting pretty close with a case statement but just couldn't close the deal. I feel like I have a decent grasp of SQL and then something like this crushes my confidence like an egg. One more question, you mention altering the table to add the columns to the table, would those columns be populated via a trigger once the original date is added? – LargeMarge Jun 28 '17 at 0:48
  • Method of population would depend on how the Business Calendar table gets updated. If it's populated one a year (or even once a month)by a short script, then I'd just fill in the warning and due dates at the end of said script. If it's normally populated once a year, but gets relevant "tweaks" during the year, you would probably want to use a trigger. And, I should note, I don't usually work in Oracle; while this is tested and works, I can't promise it's the best possible method - just that it's tested and works. – RDFozz Jun 28 '17 at 1:23
  • Is it me or is it just crazy complicated? I'm not a really a trigger guy since its PL/SQL, just keys and current date stuff. Would that trigger look similar to the above solution? – LargeMarge Jun 28 '17 at 19:06
  • Well, no, I thought about it and had to make it more complicated - see update. :-) – RDFozz Jun 28 '17 at 19:45
  • OK, will do :-) – LargeMarge Jun 29 '17 at 0:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.