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!

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.