By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table orders (order_num, part, status) as
select 1, 'A', 'Planned' from dual union all
select 2, 'B', 'Released' from dual union all
select 3, 'A', 'Closed' from dual union all
select 4, 'B', 'Released' from dual union all
select 5, 'C', 'Closed' from dual
5 rows affected
create table order_history (timestamp, order_num, message, newstatus) as
select '10:00am', 1, 'Created', 'Planned' from dual union all
select '10:15am', 2, 'Created', 'Planned' from dual union all
select '10:16am', 2, 'Status Changed', 'Released' from dual union all
select '10:20am', 3, 'Created', 'Planned' from dual union all
select '10:24am', 3, 'Status Changed', 'Released' from dual union all
select '10:25am', 2, 'Status Changed', 'Planned' from dual union all
select '10:30am', 4, 'Created', 'Planned' from dual union all
select '10:35am', 3, 'Status Changed', 'Closed' from dual union all
select '10:40am', 5, 'Created', 'Planned' from dual union all
select '10:45am', 4, 'Status Changed', 'Released' from dual union all
select '10:50am', 5, 'Status Changed', 'Released' from dual union all
select '10:55am', 2, 'Status Changed', 'Released' from dual union all
select '11:00am', 5, 'Status Changed', 'Planned' from dual union all
select '11:05am', 5, 'Status Changed', 'Released' from dual union all
select '11:15am', 5 , 'Status Changed', 'Closed' from dual union all
select '11:20am', 3, 'Status Changed', 'Released' from dual union all
select '11:25am', 3, 'Status Changed', 'Closed' from dual
17 rows affected
SELECT m.order_num,
o.part,
m.status,
m.cdate,
m.frdate,
m.lrdate,
m.cddate
FROM order_history
MATCH_RECOGNIZE(
PARTITION BY order_num
ORDER BY timestamp
MEASURES
LAST(newstatus) AS status,
FIRST(timestamp) AS cdate,
FIRST(released.timestamp) AS frdate,
LAST(released.timestamp) AS lrdate,
LAST(closed.timestamp) AS cddate
PATTERN (created (planned|released|closed)*)
DEFINE
created AS message = 'Created',
planned AS message = 'Status Changed' AND newstatus = 'Planned',
released AS message = 'Status Changed' AND newstatus = 'Released',
closed AS message = 'Status Changed' AND newstatus = 'Closed'
) m
INNER JOIN orders o
ON o.order_num = m.order_num
| ORDER_NUM | PART | STATUS | CDATE | FRDATE | LRDATE | CDDATE |
|---|---|---|---|---|---|---|
| 1 | A | Planned | 10:00am | null | null | null |
| 2 | B | Released | 10:15am | 10:16am | 10:55am | null |
| 3 | A | Closed | 10:20am | 10:24am | 11:20am | 11:25am |
| 4 | B | Released | 10:30am | 10:45am | 10:45am | null |
| 5 | C | Closed | 10:40am | 10:50am | 11:05am | 11:15am |