Hi All,
Apologies I probably have some terminology wrong here. I have a table containing order information. Each row represents a line item in an order, for example 1 order ID may have 3 items. Each row item has an order status, shipped, placed or returned.
I would like to run a count split by the order date to understand the overall order status. Where if all item in a given order ID have been returned the new field let's call it OVERALL_ORDER_STATUS = RETURNED. If one or more item in a given order ID is RETURNED but the rest either PLACED or SHIPPED then OVERALL_ORDER_STATUS = Partial Return. And lastly if all line items are SHIPPED then OVERALL_ORDER_STATUS = Shipped.
What SQL command should I be looking for/Researching?
Example data:
Order_ID = 123456 | ITEM_ID = AEFGGH ORDER_STATUS = SHIPPED
Order_ID = 123456 | ITEM_ID = AEFNMM ORDER_STATUS = RETURNED
Order_ID = 123456 | ITEM_ID = CCCLLZ ORDER_STATUS = SHIPPED
The above rolled up would be OVERALL_ORDER_STATUS = Partial Return
Thanks!