I would like to modify a stored procedure that uses multiple CTEs. The code works good, but I noticed something that needs to be added to the code, but I have not figured out how, and would like to get some help if possible.
Using the data and scripts I uploaded, I see that order S20026, with Original Quantity of 40,000, gets updated (by user) to 23,000, then the quantity shipped is 24,450
The code uses the 40,000 as the Original Quantity to calculate the Filled Rate, and that part of the code works excellent, but what would I need to "tweak" to the code is that for Orders like this one, with multiple changes in the Order Quantity, I want to use the last change that is not equal to the Quantity Shipped as the "Original Quantity" for order S20026 that would be 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
39,000 = to ship qty of 39,000 then 38,000 is the original quantity.
Filled Rate = (39000 / 38,000) * 100 = 102.6
The code as is, already works well for order S19557, and S19856.
I think I just need to modify this CASE statement:
WHEN cte2.ShippedQuantity = 0
END AS OrgQty
But I have not made it work yet.
The last ordered quantity comes from the last time an order quantity got updated that is not equal to the final shipped quantity. for order S20026, the last ordered quantity (requested by client) was 23,000 and the filled rate would be much higher using that qty than the 40,000
At any rate, thank you greatly for all your help.