Solved

Modifying a procedure that uses CTEs

Posted on 2014-04-24
6
196 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
[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
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 41

Expert Comment

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

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
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

by:Sharath
ID: 40028466
Use COALESCE or ISNULL to replace null with 100
0
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 

Author Comment

by:metropia
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

by:
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

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
ID: 40059604
Thank you Sharath. You have saved me big time!
0

Featured Post

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

622 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