0

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 ?

  • In your source table each events date posesses between two adjacent dates in contract and backward. Is it a dogma, or it is possible that, for example, there is two (or none) contract rows between adjacent events rows? If first then show according constraints which provides this (the best way - post full DDL pack for data storage), if last then show desired output for such situations. – Akina Sep 8 at 4:38
  • Idea - UNION contracts and modiications, then join them to events by the condition that the date is not above, and select the row with greatest date. – Akina Sep 8 at 4:42
  • But how do you join with a condition of greatest ? – Mickael V. Sep 8 at 9:39
  • JOIN and ROW_NUMBER in CTE, then rn=1 in main query. – Akina Sep 8 at 9:58
0

Okay, if I'm reading your requirements correctly, this should work (or can be made to work without too much effort). If it isn't already, the primary key should be entity_id + the date column, or at least a UNIQUE constraint added for those two columns. For optimal performance, the tables should be index-organized on entity_id + the appropriate date column.

SELECT
  date_event
 ,entity_id
 ,CASE
    WHEN data_source = 'Mod' THEN date_modification
    ELSE date_contract
  END AS date_situation
 ,CASE
    WHEN data_source = 'Mod' THEN capacity_mod
    ELSE capacity_con
  END AS capacity
 ,month_capacity
 ,used
FROM
  (
    SELECT
      Events.date_event
     ,Events.entity_id
     ,Events.used
     ,Con.date_contract 
     ,Con.capacity AS capacity_con
     ,Mod.date_modification
     ,Mod.capacity as capacity_mod
     ,Mod.month_capacity
     ,CASE
        WHEN 
          EXTRACT(YEAR from Mod.date_modification) = EXTRACT(YEAR from Events.date_event)
            AND EXTRACT(MONTH from Mod.date_modification) = EXTRACT(MONTH from Mods.date_modification)
            AND COALESCE(Con.date_contract,DATE'1901-01-01') < Mod.date_modification 
        THEN 'Mod'
        ELSE 'Con' 
      END AS data_source
    FROM
      Events Events
    LEFT JOIN
      Contract Con
        ON Con.entity_id = Events.entity_id
            AND Con.date_contract =
              (
                SELECT
                  MAX(date_contract)
                FROM
                  Contract
                WHERE
                  entity_id = Events.entity_id
                    AND date_contract <= Events.date_event
          )
    LEFT JOIN
      Modifications Mod
        ON Mod.entity_id = Events.entity_id
             AND Mod.date_modification =
               (
                SELECT
                   MAX(date_modification)
                 FROM
                   Modifications
                 WHERE
                   entity_id = Events.entity_id
                     AND date_modification <= Events.date_event
               )
  ) summary

Random shower thought: It is entirely possible to add the necessary logic to the join condition for Modifications:

LEFT JOIN
  Modifications Mod
    ON Mod.entity_id = Events.entity_id
        AND Mod.date_modification =
          (
            SELECT
              MAX(date_modification)
            FROM
              Modifications
            WHERE
              entity_id = Events.entity_id
                AND date_modification <= Events.date_event
                AND EXTRACT(YEAR FROM date_modification) = EXTRACT(YEAR FROM Events.date_event)
                AND EXTRACT(MONTH FROM date_modification) = EXTRACT(MONTH FROM Events.date_event)
                AND COALESCE(Con.date_contract,DATE'1901-01-01') < date_modification 
          )

Then the logic for each column is much simpler:

  COALESCE(Mod.date_modification,Con.date_contract) AS date_situation
 ,COALESCE(Mod.capacity,Con.capacity) AS capacity

I avoided this because:

  1. Increases likelihood query optimizer might try to do something stupid. At best the plan is the same.
  2. Having the filtering logic in the sub-select buries it out of sight, and makes it more likely someone will screw up the base join condition trying to edit something else.
  3. The motivation for the selection and the idea that you have two potential sources for certain columns is less obvious.

So yes, first method is more code, but it's not a simple requirement and I tend to value readability/maintainability over fancy unless there's some sort of performance benefit.

| improve this answer | |

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.