Member Avatar for 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!

Edited by Aldius because: n/a
  • 2 Contributors
  • 1 Reply
  • 217 Views
  • 3 Days Discussion Span
  • Latest Post Latest Post by rch1231

Dani AI

Generated

A reliable pattern is: first aggregate total quantity per product, then pick the single VolumePricing row whose BreakLevel is the largest value that does not exceed that total. Compute the discounted price as Price - Amount and fall back to Price when no break level applies. That preserves products with no qualifying break level and guarantees the amount comes from the correct break row.

WITH ProdQty AS (
  SELECT op.ProdID,
         SUM(op.Quantity) AS TotQuantity,
         p.Price
  FROM OrderPreBuild op
  JOIN Products p ON p.UID = op.ProdID
  WHERE op.SessionID = @SessionID
  GROUP BY op.ProdID, p.Price
)
SELECT q.ProdID,
       q.TotQuantity,
       q.Price AS BasePrice,
       COALESCE(v.Amount, 0) AS MatchedAmount,
       COALESCE(q.Price - v.Amount, q.Price) AS FinalPrice
FROM ProdQty q
OUTER APPLY (
  SELECT TOP (1) Amount
  FROM VolumePricing vp
  WHERE vp.ProductID = q.ProdID
    AND vp.BreakLevel <= q.TotQuantity
  ORDER BY vp.BreakLevel DESC, vp.Amount DESC
) v;

Notes and why this is preferable to 's aggregate trick: using MAX(Amount) without explicitly selecting the row with the highest eligible BreakLevel can give the wrong discount (or lose products entirely if a WHERE filter removes them). OUTER APPLY preserves all products and deterministically returns the single best break row (or NULL when none match), so the COALESCE(..., q.Price) fallback is straightforward.

Troubleshooting / extras: ensure numeric types and rounding are correct; if Price may vary in source rows, join Products after aggregation or use MAX(p.Price) deliberately; add an index on VolumePricing(ProductID, BreakLevel) for performance; break-level ties can be resolved by adding , Amount DESC to the ORDER BY as shown. This method directly implements the requirement to subtract the amount from price for the highest break level met.

Member Avatar for 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...

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.