SQL Select highest values from CTE

[Aldius]

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)

SELECT * FROM cte2

Thanks very much for your help!

[rch1231]

Hello,

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 
where 
TotQuantity >= BreakLevel 
Group By ProdID

Let me know how it goes...