• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 204
  • Last Modified:

Modifying a procedure that uses CTEs

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

Open in new window


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
metropia
Asked:
metropia
  • 3
  • 3
1 Solution
 
SharathData EngineerCommented:
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

Open in new window

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

Open in new window

0
 
metropiaAuthor Commented:
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
 
SharathData EngineerCommented:
Use COALESCE or ISNULL to replace null with 100
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
metropiaAuthor Commented:
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
 
SharathData EngineerCommented:
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

Open in new window

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

Open in new window

0
 
metropiaAuthor Commented:
Thank you Sharath. You have saved me big time!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now