I been trying to figure out a good way to solve my problem but I keep getting stuck.
I'm currently working on a OTD calculation doc. However, one requirement is to be able to calculate the OTIF (On Time in Full) as well.
We have a order, and order position.
In the OTD calculation we measure the status on order position level, and this works great.
The OTIF calculation will be on order level and this causes problem.
With help of max(date) I get the latest position(s) sent, and I load the status of that specific position. Tho, sometimes there is more than one position causing problems that might have a different status (or none at all for that matter) and this gives me multiple rows.
001 OnTime -
123 Late Out_of_stock
890 Late Out_of_stock
As you can see, order 123 is fine. However, 890 contain multiple rows due to the fact that the order have more than one position sent at max(date) and different status on different positions.
How can I prioritize or exclude those multiple rows? Is there a way to "look in the future" during the load process?