How to look up a range

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Rajah - 06 Jul 2006 17:00 GMT

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.)

Reply to this Message

Tonkuma - 07 Jul 2006 09:45 GMT

SELECT Marginal from marginalTaxRates where AGI BETWEEN AgiLo AND AgiHi

Reply to this Message

Rajah - 07 Jul 2006 16:45 GMT

> SELECT Marginal from marginalTaxRates where AGI BETWEEN AgiLo AND AgiHi

Has anyone used LOOKUP for this?

Reply to this Message

Brian Tkatch - 07 Jul 2006 17:25 GMT

> 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.

Reply to this Message

Rajah - 12 Jul 2006 23:25 GMT

> 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.

Reply to this Message

--CELKO-- - 08 Jul 2006 17:21 GMT

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);

Reply to this Message