Solved

# Modifying a procedure that uses CTEs

Posted on 2014-04-24
195 Views
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

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

The code as is, already works well for order S19557, and S19856.

I think I just need to modify this CASE statement:

``````,	CASE
WHEN cte2.ShippedQuantity = 0
THEN 0
ELSE cte_oqty.QuantityOrdered
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.
script-create-insert.sql
script-cte.sql
stored-procedure.txt
0
Question by:metropia
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 3
• 3

LVL 41

Expert Comment

ID: 40028191
try this.
``````;WITH CTE1
AS (SELECT *,
ROW_NUMBER()
OVER (
PARTITION BY SalesOrderNumber, ItemNumber, QuantityOrdered
ORDER BY VersionNumber) rn1
FROM Orders),
CTE2
AS (SELECT *,
ROW_NUMBER()
OVER (
PARTITION BY SalesOrderNumber, ItemNumber
ORDER BY VersionNumber DESC) rn2
FROM CTE1 c1
WHERE rn1 = 1)
SELECT c1.SalesOrderNumber,
c1.ItemNumber,
c1.OrderDate,
c1.RequestedDeliveryDate,
c1.PromisedDeliveryDate,
c1.QuantityOrdered,
c2.PostingDate,
c2.QuantityShipped,
c1.QuantityOrdered*100.0/c3.QuantityOrdered FilledRate
FROM CTE2 c1
LEFT JOIN (SELECT *
FROM Orders
WHERE QuantityShipped <> 0) c2
ON c1.SalesOrderNumber = c2.SalesOrderNumber
AND c1.ItemNumber = c2.ItemNumber
AND c1.rn2 = 1
LEFT JOIN (SELECT *
FROM CTE2
WHERE rn2 = 2) c3 ON c1.SalesOrderNumber = c3.SalesOrderNumber
AND c1.ItemNumber = c3.ItemNumber  AND c1.rn2 = 1
ORDER BY c1.SalesOrderNumber,
c1.ItemNumber,
c1.VersionNumber
``````
My results are
``````SalesOrderNumber	ItemNumber	OrderDate	RequestedDeliveryDate	PromisedDeliveryDate	QuantityOrdered	PostingDate	QuantityShipped	FilledRate
S19856	10526	2014-02-13 00:00:00.000	2014-03-10 00:00:00.000	2014-03-10 00:00:00.000	7650.000000000000000000	2014-03-09 00:00:00.000	7650.000000000000000000	NULL
S19856	10568	2014-02-13 00:00:00.000	2014-03-14 00:00:00.000	2014-03-14 00:00:00.000	22000.000000000000000000	2014-03-09 00:00:00.000	22000.000000000000000000	NULL
S19856	12309	2014-02-13 00:00:00.000	2014-03-14 00:00:00.000	2014-03-14 00:00:00.000	10000.000000000000000000	2014-03-09 00:00:00.000	10000.000000000000000000	NULL
S20026	10568	2014-02-21 00:00:00.000	2014-03-14 00:00:00.000	2014-03-14 00:00:00.000	40000.000000000000000000	NULL	NULL	NULL
S20026	10568	2014-02-21 00:00:00.000	2014-03-14 00:00:00.000	2014-03-14 00:00:00.000	23000.000000000000000000	NULL	NULL	NULL
S20026	10568	2014-02-21 00:00:00.000	2014-03-14 00:00:00.000	2014-03-14 00:00:00.000	24450.000000000000000000	2014-03-14 00:00:00.000	24450.000000000000000000	106.304347
``````
0

Author Comment

ID: 40028332
Hi Sharath,

the results for order's s19557 and s20026 scenarios are great.

order s19856 shows 'null' on the filled rate col. when it should be 100%
0

LVL 41

Expert Comment

ID: 40028466
Use COALESCE or ISNULL to replace null with 100
0

Author Comment

ID: 40029624
Hi Sharath,

I did the ISNULL function and I think that can work for now with my problem.

I still find another problem, the line that contains the filled rate

example:

S20026      10568      2014-02-21 00:00:00.000      2014-03-14 00:00:00.000      2014-03-14 00:00:00.000      24450.000000000000000000      2014-03-14 00:00:00.000      24450.000000000000000000

It uses 24,450 as the quantity ordered, but it should show 23,000 because it uses that quantity to calculate the filled rate and it will be the line to be displayed on the report. I would like to see that quantity, the quantity used for  the filled rate calc. as the quantity ordered.

I hope this makes sense.

Thank you very much for your help.
0

LVL 41

Accepted Solution

Sharath earned 500 total points
ID: 40031272
It uses 23,000 for FillRate calculation but not displaying. Per your original request (refer your mockup.xls in previous question), I displayed the Quantity Shipped as 24450.000000000000000000. Do you want to display 23,000 also? You can try like this.
Remove the unwanted columns if any.
``````;WITH CTE1
AS (SELECT *,
ROW_NUMBER()
OVER (
PARTITION BY SalesOrderNumber, ItemNumber, QuantityOrdered
ORDER BY VersionNumber) rn1
FROM Orders),
CTE2
AS (SELECT *,
ROW_NUMBER()
OVER (
PARTITION BY SalesOrderNumber, ItemNumber
ORDER BY VersionNumber DESC) rn2
FROM CTE1 c1
WHERE rn1 = 1)
SELECT c1.SalesOrderNumber,
c1.ItemNumber,
c1.OrderDate,
c1.RequestedDeliveryDate,
c1.PromisedDeliveryDate,
c1.QuantityOrdered,
c2.PostingDate,
c2.QuantityShipped,
ISNULL(c1.QuantityOrdered*100.0/c3.QuantityOrdered,100.0) FilledRate,
c3.QuantityOrdered FilledRateQuantityOrdered
FROM CTE2 c1
LEFT JOIN (SELECT *
FROM Orders
WHERE QuantityShipped <> 0) c2
ON c1.SalesOrderNumber = c2.SalesOrderNumber
AND c1.ItemNumber = c2.ItemNumber
AND c1.rn2 = 1
LEFT JOIN (SELECT *
FROM CTE2
WHERE rn2 = 2) c3 ON c1.SalesOrderNumber = c3.SalesOrderNumber
AND c1.ItemNumber = c3.ItemNumber  AND c1.rn2 = 1
ORDER BY c1.SalesOrderNumber,
c1.ItemNumber,
c1.VersionNumber
``````
Results:
``````SalesOrderNumber	ItemNumber	OrderDate	RequestedDeliveryDate	PromisedDeliveryDate	QuantityOrdered	PostingDate	QuantityShipped	FilledRate	FilledRateQuantityOrdered
S19856	10526	2014-02-13 00:00:00.000	2014-03-10 00:00:00.000	2014-03-10 00:00:00.000	7650.000000000000000000	2014-03-09 00:00:00.000	7650.000000000000000000	100.000000	NULL
S19856	10568	2014-02-13 00:00:00.000	2014-03-14 00:00:00.000	2014-03-14 00:00:00.000	22000.000000000000000000	2014-03-09 00:00:00.000	22000.000000000000000000	100.000000	NULL
S19856	12309	2014-02-13 00:00:00.000	2014-03-14 00:00:00.000	2014-03-14 00:00:00.000	10000.000000000000000000	2014-03-09 00:00:00.000	10000.000000000000000000	100.000000	NULL
S20026	10568	2014-02-21 00:00:00.000	2014-03-14 00:00:00.000	2014-03-14 00:00:00.000	40000.000000000000000000	NULL	NULL	100.000000	NULL
S20026	10568	2014-02-21 00:00:00.000	2014-03-14 00:00:00.000	2014-03-14 00:00:00.000	23000.000000000000000000	NULL	NULL	100.000000	NULL
S20026	10568	2014-02-21 00:00:00.000	2014-03-14 00:00:00.000	2014-03-14 00:00:00.000	24450.000000000000000000	2014-03-14 00:00:00.000	24450.000000000000000000	106.304347	23000.000000000000000000
``````
0

Author Closing Comment

ID: 40059604
Thank you Sharath. You have saved me big time!
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question