April 23, 2014 at 8:47 am
Thank you for your response.
I will continue to try make it work.
Once question if possible, if I want to store the results of the working query in a variable table, where would I declare the table and the insert according to the current query you helped me with?
April 23, 2014 at 11:03 am
i am getting nowhere :/
whenever you have time, i would appreciate any help.
thank you
April 24, 2014 at 2:13 am
itortu (4/23/2014)
i am getting nowhere :/whenever you have time, i would appreciate any help.
thank you
here you go...this works for your sample data....probably a more eloquent solution could be written, but this just builds on what we had before
WITH cte
AS (
SELECT SalesOrderNumber,
ItemNumber,
QuantityOrdered,
MIN(VersionNumber) AS VerMin,
MAX(VersionNumber) AS VerMax
FROM SampleOrders
GROUP BY SalesOrderNumber,
ItemNumber,
QuantityOrdered
)
,
cte2 as (
SELECT cte.SalesOrderNumber,
cte.ItemNumber,
OMin.OrderDate,
OMin.RequestedDeliveryDate,
OMin.PromisedDeliveryDate,
OMin.QuantityOrdered,
OMax.PostingDate,
OMax.ShippedQuantity
FROM SampleOrders AS OMin
INNER JOIN cte ON OMin.SalesOrderNumber = cte.SalesOrderNumber
AND OMin.VersionNumber = cte.VerMin
AND OMin.ItemNumber = cte.ItemNumber
INNER JOIN SampleOrders AS OMax ON cte.SalesOrderNumber = OMax.SalesOrderNumber
AND cte.VerMax = OMax.VersionNumber
AND cte.ItemNumber = OMax.ItemNumber
)
,
cte_mvn as (
SELECT SalesOrderNumber,
ItemNumber,
MIN(VersionNumber) AS mvn
FROM SampleOrders
GROUP BY SalesOrderNumber,
ItemNumber
)
, cte_oqty as (
SELECT cte_mvn.SalesOrderNumber,
cte_mvn.ItemNumber,
SampleOrders.QuantityOrdered
FROM cte_mvn
INNER JOIN SampleOrders ON cte_mvn.SalesOrderNumber = SampleOrders.SalesOrderNumber
AND cte_mvn.ItemNumber = SampleOrders.ItemNumber
AND cte_mvn.mvn = SampleOrders.VersionNumber
)
SELECT cte2.SalesOrderNumber,
cte2.ItemNumber,
cte2.OrderDate,
cte2.RequestedDeliveryDate,
cte2.PromisedDeliveryDate,
cte2.QuantityOrdered,
cte2.PostingDate,
cte2.ShippedQuantity,
CASE
WHEN cte2.ShippedQuantity = 0
THEN 0
ELSE cte_oqty.QuantityOrdered
END AS OrgQty,
CASE
WHEN cte2.ShippedQuantity = 0
THEN 0
ELSE (cte2.ShippedQuantity / cte_oqty.QuantityOrdered) * 100
END AS fillrate
FROM cte2
INNER JOIN cte_oqty ON cte2.SalesOrderNumber = cte_oqty.SalesOrderNumber
AND cte2.ItemNumber = cte_oqty.ItemNumber
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
April 24, 2014 at 9:47 am
J Livingston SQL, you are a life saver sir.
I have a question about something I noticed, and this is probably a communication flaw of mine.
Using the data I uploaded, when I run your code, I see that order S20026 with version 1 quantity of 40,000, then it gets changed to 23,000, then the quantity shipped is 24,450
I see that 40,000 is used as the original quantity for the filled rate calculation, and that is excellent, but what would I need to tweak to your code if I want to use the 23,000 as the last changed before the quantity ships and that is not equal to the shipment quantity?
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
Your Code already works for order S19557, I am afraid to change it to meet the criteria of S20026 and mess things up.
If you give me some instruction I can try it again, but I understand if this irritates you and rather stop working on this.
I think I just need to modify this CASE statement:
,CASE
WHEN cte2.ShippedQuantity = 0
THEN 0
ELSE cte_oqty.QuantityOrdered
END AS OrgQty
At any rate, thank you greatly for all your help.
April 24, 2014 at 11:02 am
itortu (4/24/2014)
J Livingston SQL, you are a life saver sir.I have a question about something I noticed, and this is probably a communication flaw of mine.
Using the data I uploaded, when I run your code, I see that order S20026 with version 1 quantity of 40,000, then it gets changed to 23,000, then the quantity shipped is 24,450
I see that 40,000 is used as the original quantity for the filled rate calculation, and that is excellent, but what would I need to tweak to your code if I want to use the 23,000 as the last changed before the quantity ships and that is not equal to the shipment quantity?
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
Your Code already works for order S19557, I am afraid to change it to meet the criteria of S20026 and mess things up.
If you give me some instruction I can try it again, but I understand if this irritates you and rather stop working on this.
I think I just need to modify this CASE statement:
,CASEWHEN cte2.ShippedQuantity = 0
THEN 0
ELSE cte_oqty.QuantityOrdered
END AS OrgQty
At any rate, thank you greatly for all your help.
how do you propose that you determine the "last ordered" quantity...??
this is starting to look like an agile process... where the end results have not been determined and each iteration begets another solution.
can you tie down what is required ? ...otherwise this will run and run
r
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
April 24, 2014 at 12:32 pm
Honestly, your last code was awesome, I just replaced the "Sample Orders" part with the sub-query that I use to get the data and it gave me excellent results right away.
Then I noticed that order S20026 was using as Original Quantity 40,000 and noticed (like you said before) why are we even using the 23,000, and then I remembered that (answering your question):
how do you propose that you determine the "last ordered" quantity...??
the last ordered quantity comes from the last time an order quantity got updated that is not equal to the final shipped quantity. for the scenario of that order, 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
April 24, 2014 at 12:53 pm
itortu (4/24/2014)
Honestly, your last code was awesome, I just replaced the "Sample Orders" part with the sub-query that I use to get the data and it gave me excellent results right away.Then I noticed that order S20026 was using as Original Quantity 40,000 and noticed (like you said before) why are we even using the 23,000, and then I remembered that (answering your question):
how do you propose that you determine the "last ordered" quantity...??
the last ordered quantity comes from the last time an order quantity got updated that is not equal to the final shipped quantity. for the scenario of that order, 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
care to share what you have tried so far?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
April 24, 2014 at 1:16 pm
Are you asking for the stored procedure after I added your code?
What I tired so far was to modified the CASE statement where the Filled Rate gets calculated:
CASE
WHEN cte2.QuantityOrdered <> cte_oqty.QuantityOrdered
AND cte2.QuantityOrdered <> cte2.ShippedQuantity
THEN cte2.QuantityOrdered
WHEN cte2.ShippedQuantity = 0
THEN 0
ELSE cte_oqty.QuantityOrdered
END AS OrgQty
I can upload the stored procedure too if needed.
April 25, 2014 at 8:35 am
i forgot to upload the stored procedure yesterday
April 27, 2014 at 2:19 pm
Hi,
I know I went back and forth with this question several times, but it would be really great if you would help me a last time because I can't figure this out on my own. If that is possible, it would be great I don't mean to be rude, or pushy I ask help with all respect. and because honestly it took me a lot to understand what I needed to do.
Thank you very much
April 27, 2014 at 3:07 pm
itortu (4/27/2014)
Hi,I know I went back and forth with this question several times, but it would be really great if you would help me a last time because I can't figure this out on my own. If that is possible, it would be great I don't mean to be rude, or pushy I ask help with all respect. and because honestly it took me a lot to understand what I needed to do.
Thank you very much
try this
http://www.sqlservercentral.com/Forums/FindPost1564577.aspx
did this work...??
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
April 28, 2014 at 8:10 am
Hi.
The code that you gave me (the link) worked. After that I needed to add one more cte I think.
And adjustment to catch orders like
S20026, the order begins with 40,000, then changes to 23,000 and ships 24,450
In that order, the Original Quantity Ordered should be the 23,000 instead that the 40,000
The Original Quantity is that quantity that is different from the quantity that got shipped.
April 28, 2014 at 8:23 am
itortu (4/28/2014)
Hi.The code that you gave me (the link) worked. After that I needed to add one more cte I think.
And adjustment to catch orders like
S20026, the order begins with 40,000, then changes to 23,000 and ships 24,450
In that order, the Original Quantity Ordered should be the 23,000 instead that the 40,000
The Original Quantity is that quantity that is different from the quantity that got shipped.
what happens when the order quantity changes 'n times' before it is shipped...what are your rules.??
please determine all your rules.....at the moment after each iteration you provide another scenario......!
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
April 28, 2014 at 9:55 am
if an order changes multiple times before it is shipped, the original quantity is always going to be the last quantity to what it was changed last.
example
--------------
order quantity
40000
--------------
1st change
36000
--------------
2nd change
31000
--------------
3rd change
28000
--------------
4th quantity order quantity shipped
25000 25000
in this order the original quantity (last quantity) would be 28000
April 28, 2014 at 10:02 am
itortu (4/28/2014)
if an order changes multiple times before it is shipped, the original quantity is always going to be the last quantity to what it was changed last.example
--------------
order quantity
40000
--------------
1st change
36000
--------------
2nd change
31000
--------------
3rd change
28000
--------------
4th quantity order quantity shipped
25000 25000
in this order the original quantity (last quantity) would be 28000
ok ...post sample set up code for this and expected results,
thanks
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 15 posts - 31 through 45 (of 45 total)
You must be logged in to reply to this topic. Login to reply