Assuming you know how to write the sql to get all the details for the order and details for any related orders this is actually pretty simple.
case when max(case when OrderDetails.OrderStage = 'Approved' then 1 else 0 end) = 1 then 'Approved' else 'Rejected' end as NewOrderStatus
It would be better if you had the order status in a lookup table instead of storing the strings. That would make a lot of things simpler in the long run.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/