How to select earliest date with condition

Question

Is it possible to select the earliest Open_Date from the below table on the condition that Sub_ID's Status is either Open or Complete?

Order_ID	Sub_ID	Open_Date	Close_Date	Status	Status_Update_Date
5555	001	2018-07-16	NULL	Open	2018-07-16
5555	001	2018-07-16	2022-03-01	Cancelled	2022-03-01
5555	002	2022-03-25	NULL	Open	2022-03-25
5555	002	2022-03-25	2022-03-28	Complete	2022-03-28
5555	003	2022-05-01	NULL	Open	2022-05-01

The result from the above example should be 2022-03-25 (Sub_ID 002), because Sub_ID 001 ended up being Cancelled, although 001 does have an Open_Date.

Running a simple MIN query would just give me the result of 2018-07-16 and I really don't know how to code it to omit 2018-07-16 and return 2022-03-25.

SELECT 
    MIN (Open_Date) 
FROM 
    (SELECT 
         Order_ID, Sub_ID, Open_Date, Close_Date, Status, Status_Update_Date 
     FROM 
         Orders 
     WHERE 
         Order_ID = 5555) AS X

Answer 1

Please try to provide a insert statement and create statement for ease thanks.

Anyway Here is the answer.

select min(open_date)
from orders o
where o.sub_id not in (
    select sub_id
    from orders o2 
    where o2.sub_id = o.sub_id and o2.status = 'cancelled'
)
and o.order_id = '5555'

Explanation: The subquery will select the list of all the sub_id's that are cancelled and will exclude from the list so you will have a list of all sub_id's which don't have cancelled on them. And then from the list you can select the min data with order_id = '5555'