CASE expression with OR AND

case

I have 5 columns – Order, shipping, tracking, Delivery and Overallstauts.

I have have 4 status, Green means "good", Amber means "ok", Yellow means "average" and Red means "Bad".

I want worst status to be displayed in Overallstatus column out of all the status.

ProductOrdershippingtrackingDeliveryOverallstatus
Product1GreenYellowGreenRedRED
Product2GreenGreenYellowGreenYellow
Product3YellowYellowGreenGreenYellow
Product3AmberGreenGreenGreenAmber

I want this in CASE statement

Case
When Order = Green, shipping  = Yellow, tracking = Green, Delivery  = Red then Overallstaus  = Red
When Order = Amber, shipping  = Yellow, tracking = Green, Delivery  = Green then Overallstaus  = Yellow
When Order = Yellow, shipping  = Yellow, tracking = Green, Delivery  = Red then Overallstaus  = Red
When Order = Amber, shipping  = Green, tracking = Green, Delivery  = Green then Overallstaus  = Amber

Best Answer

Another solution is if you stored the Status names in your columns in a separate Statuses "enums" table, that stored the IDs in increasing order from best to worst then you can do something like the following depending on what database system you're using (T-SQL):

SELECT 
    (
        SELECT MAX(OverallStatusId)
        FROM 
        (
            VALUES (OrderStatusId),. 
                (ShippingStatusId),. 
                (TrackingStatusId), 
                (DeliveryStatusId)
        ) AS OverallStatus(OverallStatusId)
    ) AS OverallStatusId
FROM OrdersTable

Then all you'd have to do is join to your Statuses enums table on the OverallStatusId above to get the Status name.