Hello I had a question about MS-SQL. I am trying to write a query which will select a group of values from several tables (prodId, Amount, BreakLevel, TotQuantity, Price)

There are several amounts associated with different break levels. i.e. a break level of 15 might get an amount of .10, a break level of 25 might get an amount of .25 and so on. What I am attempting to do is compare the TotalQuantity to break level, to find the highest break level that total quantity exceeds. I then subtract price from the amount to produce a value. If no break level is made then I will simply get the value of price.

Each query will give me results similar to this:

BreakLevel     Amount        TotQuantity          Price     ProdID
15      	     0.05       	52      	1.20	   322
25      	     0.10	        52      	1.20   	   322
40      	     0.15	        52      	1.20	   322
15      	     0.65	        7       	11.75	   445
25      	     1.20	        7       	11.75	   445
40      	     2.00	        7       	11.75	   445
15                   0.50              17                8.75      733
25                   0.75              17                8.75      733
40                   1.00              17                8.75      733

What I would need then is to get 3 prices returned: 11.75 for prodID 445, 8.25 for 733 (since it is price - amount where the quantity exceeds breakLevel by the highest margin [i.e. 8.75 - 0.50 for having 15 or more ordered]) and 1.05 for ProdID 322 (52 products is greater than breakLevel 40 so it is price - the highest breakLevel that quantity exceeds [1.20 - 15])

I have created code to put the values that I need in a Common Table Expression, but I do not know how to get the 3 values I need from that CTE. Here is the code I used below:

;With cte As (SELECT orderprebuild.prodid, SUM(orderprebuild.quantity) AS TotQuantity, Products.Price FROM orderprebuild, Products WHERE
orderprebuild.sessionid = @SessionID AND OrderPreBuild.ProdID = Products.UID
GROUP BY orderprebuild.prodid, Products.Price),
cte2 As (SELECT VolumePricing.Amount, VolumePricing.BreakLevel, cte.TotQuantity, cte.Price, cte.prodID FROM cte INNER JOIN VolumePricing ON 
VolumePricing.ProductID = cte.ProdID)


Thanks very much for your help!

Edited by Aldius: n/a

7 Years
Discussion Span
Last Post by rch1231


If you put your code to get the first result in place of temptable in the code below it should give what you are looking for. (I think)

Select ProdID, (Max(Price) - Max(Amount) ) as NewAmount
from temptable 
TotQuantity >= BreakLevel 
Group By ProdID

Let me know how it goes...

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.