CODEWITHSUNDEEP
sqloracle-database
mkellar3
mkellar3

Reputation: 13

How to associate date events together by date

I'm working on writing a query to organize install and removal dates for car part numbers. I want to find a record of all car part installs, and removals of the same part if they have been removed from a vehicle, identified by it's VIN. I'm having trouble associating these events together because the only thing tying them together is the dates. Removals must occur after installs and another install cannot occur on the same part unless it has been removed first.

I have been able to summarize the data into separate rows by event type (e.g. each install has its own row and each removal has its own row.

What I've tried is using DECODE() by event type, but it keeps the records in separate rows. Maybe there's something COALESCE() can do here, but I'm not sure.

Here's a summary of how the data looks:

part_no | serial_no | car_vin | event_type | event_date
12345   | a1b2c3    | 9876543 | INSTALL    | 01-JAN-2019
12345   | a1b2c3    | 9876543 | REMOVE     | 01-AUG-2019
54321   | t3c4a8    | 9876543 | INSTALL    | 01-MAR-2019
12345   | a1b2c3    | 3456789 | INSTALL    | 01-SEP-2019

And here's what the expected outcome is:

part_no | serial_no | car_vin | install_date | remove_date
12345   | a1b2c3    | 9876543 | 01-JAN-2019  | 01-AUG-2019
12345   | a1b2c3    | 3456789 | 01-SEP-2019  |
54321   | t3c4a8    | 9876543 | 01-MAR-2019  |

Upvotes: 1

Views: 61

Answers (2)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59543

You can use the SQL for Pattern Matching (MATCH_RECOGNIZE):

WITH t(part_no,serial_no,car_vin,event_type,event_date) AS
    (SELECT 12345, 'a1b2c3', 9876543, 'INSTALL', DATE '2019-01-01' FROM dual
    UNION ALL SELECT 12345, 'a1b2c3', 9876543, 'REMOVE', DATE '2019-08-01' FROM dual
    UNION ALL SELECT 54321, 't3c4a8', 9876543, 'INSTALL', DATE '2019-03-01' FROM dual
    UNION ALL SELECT 12345, 'a1b2c3', 3456789, 'INSTALL', DATE '2019-09-01' FROM dual)
SELECT part_no,serial_no,car_vin, INSTALL_DATE, REMOVE_DATE
FROM t
    MATCH_RECOGNIZE (
        PARTITION BY part_no,serial_no,car_vin
        ORDER BY event_date
        MEASURES 
            FINAL MAX(REMOVE.event_date) AS REMOVE_DATE,
            FINAL MAX(INSTALL.event_date) AS INSTALL_DATE
        PATTERN ( INSTALL REMOVE? )
        DEFINE
            REMOVE AS event_type = 'REMOVE',
            INSTALL AS event_type = 'INSTALL'
    )
ORDER BY part_no, INSTALL_DATE, REMOVE_DATE;


+--------------------------------------------------+
|PART_NO|SERIAL_NO|CAR_VIN|INSTALL_DATE|REMOVE_DATE|
+--------------------------------------------------+
|12345  |a1b2c3   |9876543|01.01.2019  |01.08.2019 |
|12345  |a1b2c3   |3456789|01.09.2019  |           |
|54321  |t3c4a8   |9876543|01.03.2019  |           |
+--------------------------------------------------+

The key clause here is PATTERN ( INSTALL REMOVE? ). It means, you have exactly one INSTALL event followed by zero or one REMOVE event.

If you can have more than just one INSTALL event then use PATTERN ( INSTALL+ REMOVE? )

If you can have more than just one INSTALL event and optionally more than one REMOVE event then use PATTERN ( INSTALL+ REMOVE* )

You can simply add more events, e.g. ORDER, DISPOSAL, etc.

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522346

We can use pivoting logic here:

SELECT
    part_no,
    serial_no,
    car_vin,
    MAX(CASE WHEN event_type = 'INSTALL' THEN event_date END) AS install_date,
    MAX(CASE WHEN event_type = 'REMOVE'  THEN event_date END) AS remove_date
FROM yourTable
GROUP BY
    part_no,
    serial_no,
    car_vin
ORDER BY
    part_no;

enter image description here

Demo

This approach is a typical way to transform a key value store table, which is basically what your table is, into the output you want to see.

Upvotes: 3

Related Questions