Best way to lookup a value from multiple ranges

I need to determine what range a value fits in order to determine the appropriate discount. Just trying to determine the best most efficient way to do this in Access (2016). Here is a simplified table:

enter image description here

My first thought is to loop through a recordset to evaluate each record for >= FROM$ and =< TO$ until a range match is found, then grab the discount rate. Would there be a more efficient way to do this with an SQL statement?

3 answers

  • answered 2018-11-08 06:30 Tim Biegeleisen

    We can try using BETWEEN here:

    SELECT
        MAX(IIF(price BETWEEN FROM$ AND TO$, Discount, NULL)) AS Discount
    FROM yourTable;
    

    We might more typically expect that your table of discounts would store information for multiple groups, e.g. products. In that case, we could write:

    SELECT
        product,
        MAX(IIF(price BETWEEN FROM$ AND TO$, Discount, NULL)) AS Discount
    FROM yourTable
    GROUP BY
        product;
    

  • answered 2018-11-08 06:52 Salman A

    In your example it is impossible to find the discount for a value such as 9.999... it is greater than 9.99 and less than 10 and does not fit inside any of your ranges. I would start by changing your data to:

    | From | To   | Discount |
    |------|------|----------|
    | 0    | 10   | 0.05     |
    | 10   | 20   | 0.10     |
    | 20   | 50   | 0.12     |
    | 50   | NULL | 0.13     |
    

    And then you could probe the table like so:

    SELECT Discount 
    FROM   Discounts
    WHERE  (@Value >= [From])
    AND    (@Value <  [To] OR [To] IS NULL)
    

  • answered 2018-11-08 12:47 Gordon Linoff

    Assuming you have no gaps in the ranges, I find that in many databases the most efficient method is:

    select t.*,
           (select top 1 d.discount
            from discounts d
            where t.[$] <= d.[from$]  -- the $ is for whatever column name you actually use
            order by d.[from$] desc
           ) as discount
    from t;
    

    (Note that this ignores to$.)

    In particular, this should be able to take advantage of an index on discounts(from$, discount).