itortu (4/24/2014)

---

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.