Calculate Order Balance

[danielle_v]

Hi All,

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.

Any pointers would be greatly appreciated!

[crusader_ · Accepted solution]

Dear Danielle,

It'd be better if you provide some examples in excel of your data sample (fake data) and expected results.

There you can describe all possible cases, constraints etc...

It helps other people do not waste time for understanding of many-many-many rows of description text.

I've made an example according to your explanation, hope it helps you.

PFA

Regards,

Andrei

[danielle_v]

Hi Andrei,

Your example is perfect and does exactly what I need it to - many thanks for your help!