asked on

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

Sharath S

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

metropia

ASKER

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%

Sharath S

Use COALESCE or ISNULL to replace null with 100

metropia

ASKER

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.

ASKER CERTIFIED SOLUTION

Sharath S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

metropia

ASKER

Thank you Sharath. You have saved me big time!