Solved

Modifying a procedure that uses CTEs

Posted on 2014-04-24
6
173 Views
Last Modified: 2014-05-12
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
Comment
Question by:metropia
  • 3
  • 3
6 Comments
 
LVL 40

Expert Comment

by:Sharath
Comment Utility
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
 

Author Comment

by:metropia
Comment Utility
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 40

Expert Comment

by:Sharath
Comment Utility
Use COALESCE or ISNULL to replace null with 100
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:metropia
Comment Utility
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 40

Accepted Solution

by:
Sharath earned 500 total points
Comment Utility
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
 

Author Closing Comment

by:metropia
Comment Utility
Thank you Sharath. You have saved me big time!
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Read about achieving the basic levels of HRIS security in the workplace.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now