Convert tax like IRS function to SQL

Question

Trying to convert this tax-like IRS function http://stackoverflow.com/questions/1817920/calculating-revenue-share-at-different-tiers to SQL. The assumption is the table would be one column (price) and the tiers will be added dynamically.

My first attempt has some case statements that didn't work out well. I have since scrapped it :) Thanks for the help!

Answer 1

You could create a MYSQL user defined function. http://dev.mysql.com/doc/refman/5.1/en/adding-functions.html

Answer 2

SQL isn't really suited to iterative loops. I'd

select * from `brackets` where `tier` < $income

From there I'd... well, you linked to some sample code in another language yourself. You should really do it that way.

If you insist otherwise, SQL can do it:

DELIMITER $$
create procedure `calcTax`(IN v_param1 int)

begin
declare v_tier int;
declare v_rate decimal(3,3);
declare v_untaxed int default 0;
declare v_taxTotal float default 0;

set v_untaxed = v_param1;
set v_taxTotal = 0;

while v_untaxed > 0 do
select max(`tier`), max(`rate`) into v_tier, v_rate
    from `brackets` where tier < v_untaxed order by `tier` desc limit 1;

set v_taxTotal = v_taxTotal + (v_untaxed - v_tier) * v_rate;
set v_untaxed = v_tier;
end while;

select v_taxTotal;
end;
$$

Correct datatypes are up to you. You'll need a tax brackets table that works out. I made one for testing below (with poorly advised data types).

create table brackets (tier int primary key, rate float);
insert into brackets values (0, 0), (10000, 0.1), (50000, 0.2);