Oracle 23c

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
 ,max(oh.timestamp) lrDate
 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) lr
ON c.order_num = lr.order_num and c.part = lr.part and c.status = lr.status
LEFT JOIN 
(SELECT 
 o.order_num
 ,o.part
 ,o.status
 ,max(oh.timestamp) cdDate
 FROM orders o
 JOIN order_history oh
 ON o.order_num = oh.order_num
 WHERE oh.message = 'Status Changed' AND oh.NewStatus = 'Closed'
 group by o.order_num, o.part, o.status) cd
ON c.order_num = cd.order_num and c.part = cd.part and c.status = cd.status
ORDER BY 1, 2, 3


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
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