J Livingston SQL, you are a life saver sir.
I have a question about something I noticed, and this is probably a communication flaw of mine.
Using the data I uploaded, when I run your code, I see that order S20026 with version 1 quantity of 40,000, then it gets changed to 23,000, then the quantity shipped is 24,450
I see that 40,000 is used as the original quantity for the filled rate calculation, and that is excellent, but what would I need to tweak to your code if I want to use the 23,000 as the last changed before the quantity ships and that is not equal to the shipment quantity?
Example:
Order S20026
Order Quantity
40,000
23,000
24,450 = to ship qty of 24,450 then 23,000 is the original quantity.
Filled Rate = (24450 / 23,000) * 100 = 106.3
Order S19557
Order Quantity
38,000
39,000 = to ship qty of 39,000 then 38,000 is the original quantity.
Filled Rate = (39000 / 38,000) * 100 = 102.6
Your Code already works for order S19557, I am afraid to change it to meet the criteria of S20026 and mess things up.
If you give me some instruction I can try it again, but I understand if this irritates you and rather stop working on this.
I think I just need to modify this CASE statement:
,CASE
WHEN cte2.ShippedQuantity = 0
THEN 0
ELSE cte_oqty.QuantityOrdered
END AS OrgQty
At any rate, thank you greatly for all your help.