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', 'Close' 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
c.order_num
,c.part
,c.status
,c.cDate
,fr.frDate
,lr.lrDate
,cd.cdDate
FROM
(SELECT
o.order_num
,o.part
,o.status
,min(oh.timestamp) cDate
FROM orders o
JOIN order_history oh
ON o.order_num = oh.order_num
WHERE oh.message = 'Created' AND oh.NewStatus = 'Planned'
group by o.order_num, o.part, o.status) c
LEFT JOIN
(SELECT
o.order_num
,o.part
,o.status
,min(oh.timestamp) frDate
FROM orders o
JOIN order_history oh
ON o.order_num = oh.order_num
WHERE oh.message = 'Status Changed' AND oh.NewStatus = 'Released'
group by o.order_num, o.part, o.status) fr
ON c.order_num = fr.order_num and c.part = fr.part and c.status = fr.status
LEFT JOIN
(SELECT
o.order_num
,o.part
,o.status
| 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 | Close | 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 |
SELECT
o.order_num
,o.part
,o.status
,MIN(CASE WHEN oh.message = 'Created' AND oh.NewStatus = 'Planned' THEN oh.timestamp END) AS cDate
,MIN(CASE WHEN oh.message = 'Status Changed' AND oh.NewStatus = 'Released' THEN oh.timestamp END) AS frDate
,MAX(CASE WHEN oh.message = 'Status Changed' AND oh.NewStatus = 'Released' THEN oh.timestamp END) AS lrDate
,MAX(CASE WHEN oh.message = 'Status Changed' AND oh.NewStatus = 'Closed' THEN oh.timestamp END) AS cdDate
FROM orders o
LEFT JOIN order_history oh
ON o.order_num = oh.order_num
GROUP BY o.order_num, o.part, o.status
ORDER BY o.order_num, o.part, o.status
| 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 | Close | 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 |