Solved Quantity Breaks

[MattBaldry]

Hi All,

I am working on a quantity price break query and wondered if there is a an easy way for the below.

I will have 8 breaks of 1, 2, 5, 10, 25, 50, 100, 250. I would like the expression to take the QuoteQuantity (IE 32) and quickly find where that sits in the price breaks. As in >25 AND < 50

In the past I have used nested IIf statements but I am hoping there is a better way.

~Matt

 

[cheekybuddha]

You don't give us any info about your tables, so this is just a guess, but maybe something like:

SELECT
  q.ID,
  q.Quantity
  (
    SELECT TOP 1
      p.Price
    FROM PriceBreaks p
    WHERE q.Quantity < p.Break
    ORDER BY
      p.Break DESC
  ) AS Price
FROM Quotes q
;

 

[CJ_London]

easiest way is to store the values in a table, then use a subquery

you said

As in >25 AND < 50

that should be

As in >=25 AND < 50

or

As in >25 AND <= 50

otherwise a quantity of 25 could be assigned a wrong break

assuming the former, the subquery would be

MaxBreak: (SELECT MAX(Break) FROM tblBreaks WHERE Break<myquery.QuoteQuantity)

if you decide to add or change breaks in the future, no recoding required, just modify the table

you might need additional fields in tblBreaks and modify the subquery if these quantities can vary by product, customer or over time

 

[MattBaldry]

easiest way is to store the values in a table, then use a subquery

you said



that should be

As in >=25 AND < 50

or

As in >25 AND <= 50

otherwise a quantity of 25 could be assigned a wrong break

assuming the former, the subquery would be

MaxBreak: (SELECT MAX(Break) FROM tblBreaks WHERE Break<myquery.QuoteQuantity)

if you decide to add or change breaks in the future, no recoding required, just modify the table

you might need additional fields in tblBreaks and modify the subquery if these quantities can vary by product, customer or over time

Thanks CJ,

That is perfect and allows expansion for future price breaks, or changes to the existing ones.

Thank you

~Matt