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!