How to look up a range

[Rajah]

Give a table with a lower bounds, upper bounds, and targets, I would
like to select on a value and supply the target. For example, think
about marginal tax rates:

AgiLo AgiHi Marginal
00 14600 10%
14601 59400 15%
59401 119950 25%
119951 182800 28%
182801 326450 33%
326451 infinity 35%

A straightforward approach would be
SELECT Marginal from marginalTaxRates where AGI >=AgiLo AND AGI <=
AgiHi

If I have to look up a lot of these Marginal tax rates, is there a more
efficient way to do this? (There might not be; I'm looking for
suggestions.)

[Tonkuma]

SELECT Marginal from marginalTaxRates where AGI BETWEEN AgiLo AND AgiHi

[Rajah]

Tonkuma wrote:

SELECT Marginal from marginalTaxRates where AGI BETWEEN AgiLo AND AgiHi

Has anyone used LOOKUP for this?

[Brian Tkatch]

Tonkuma wrote:

SELECT Marginal from marginalTaxRates where AGI BETWEEN AgiLo AND AgiHi

Yeah, BETWEEN is the way to go with that SETUP.

Except "infinity" is not a number, but assuming this is for the real
world, noone will ever have the amaximum capacity of the field.

Just a note. If AgiLo is always (zero or ) one more than AgiHi, AgiHi
is inferred, and unneeded.

SELECT MAX(Marginal) from marginalTaxRates where AGI >=AgiLo

Of course >= is wasteful. :) Instead, it could be a field (Limit) whose
values are 0, then all the AgiHis besides "infinity".

SELECT MAX(Marginal) from marginalTaxRates where AGI Limit

B.

[--CELKO--]

Change the last row to
(326451, NULL, 0.35)

NULL is now your "infinity" symbol. Use between for readability

SELECT marginal
FROM MarginalTaxRates
WHERE agi BETWEEN agi_lo
AND COALESCE (agi_hi, agi);

[Rajah]

Brian Tkatch wrote:

SELECT MAX(Marginal) from marginalTaxRates where AGI >=AgiLo

Yes, this would work if we always have a progressive system, where
Marginal rates are increasing. Unfortunately, I cannot make that
assumption with the real data.

You do have an excellent observation about the problem of
non-continguous data. After all, it is possible to have a
dollars-and-cents AGI that falls between the cracks.