#StackBounty: #oracle #join #oracle-12c #view CREATE VIEW WITH LEFT JOIN – FILTER RIGHT DEPENDING ON LEFT

Bounty: 50

I am using Oracle 12.1.
I have the following tables, simplified for the question :
(Note that the XXX for entity ID does represent the same ID being repeated)

Events

date_event | entity_id | used
-------------------------------
2020-09-01 | XXX       | ...
2020-08-15 | XXX       | ...
2020-07-01 | XXX       | ...
...        | ...       | ...

Contract

date_contract | entity_id | capacity
-------------------------------------
2020-08-25    | XXX       | 1000
2020-07-20    | XXX       | 1000
2020-06-22    | XXX       | 1000
...

Modifications

date_modification | entity_id | capacity | month_capacity
-------------------------------------------------------------
2020-08-10        | XXX       | 2000     | 500

I would like to create a view like this :

date_event | entity_id | date_situation | capacity | month_capacity | used
--------------------------------------------------------------------------
2020-09-01 | XXX       | 2020-08-25     | 1000     | NULL           | 200
2020-08-15 | XXX       | 2020-08-10     | 2000     | 500            | 200
2020-07-01 | XXX       | 2020-06-22     | 1000     | NULL           | 200

For one entity_id :

  • Events table has one record per day
  • Contracts has one record per month, but technically could be whenever, there is no business rule about being monthly
  • Modifications table has one record when an edit is made ; can be once a month, once a year, once a week, never, …

The fields date_situation, capacity, and month_capacity come from either Contracts or Modifications depending on the following conditions :

  • The first (ordering by date_contract) record in Contracts that has a date_contract more recent than date_event, if there is no record in Modifications with a more recent date_modification in the same month. If there is such a record, then it comes from Modifications.
  • If there is neither a more recent Contracts or Modifications record, then we want the most recent one between the most recent Contracts record and the most recent Modifications record. But it can be the most recent Modifications record only if it is in the same month as date_event.

So, to sum it up, data comes from the most recent record in Contracts for the same entity_id, unless there is a more recent Modifications record, in the same month as the record from Events.
And if so, we want the most recent Modifications record of the same month.

I hope the explaination and the example view are clear enough. So far we have something with (SELECT) LEFT JOIN (SELECT) LEFT JOIN (SELECT) but it takes the most recent Contracts and Modifications records in the table. I need them relative to date_event, and I can’t seem to "inject" that in the subqueries.

How would one proceed to represent such a view ?


Get this bounty!!!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.