I have three tables - Order, Shipment and Delivery. Order joins to Shipment, and Shipment joins to Delivery.
I have created a fourth table in which I am trying to calculate the OrderStatus, where I have pulled in the following fields;
* OrderNumber (which is a field that appears in all three tables)
* OrderQuantity (from the order table)
* ShipmentDate and ShipmentQuantity from the Shipment table
* DeliveryDate and DeliveryQuantity from the Delivery table
From this, I have been able to calculate OrderStatus - if the Order has a DeliveryDate it is DELIVERED, if it has no DeliveryDate but it has a ShipmentDate it is SHIPPED and if it has no ShipmentDate then it is UNSHIPPED.
However, I need to also be able to calculate the order balance as part of this, as an OrderNumber may not all ship at once, so may have more than one status.
For example there may be an OrderNumber with an OrderQuantity of 1000. If there is a ShipmentQuantity of 500, I need to be able to see 500 units as UNSHIPPED and 500 units as SHIPPED. Similarly, an OrderNumber could have an OrderQuantity of 3000 where1000 units have been delivered, 1000 have been shipped but not delivered and the remaining 1000 have yet to ship. In this instance, I'd need to see 1000 units DELIVERED, 1000 units SHIPPED and 1000 units UNBOOKED.