Hack 39. Apply a Progressive Tax

A progressive tax varies with the amount earned. For example, you might pay 10 percent on the first $10,000 you earn but 15 percent on any earnings over $10,000.

Suppose that the tax bands (the tax rates for a given income level) are as shown in Table 5-21.

Table 5-21. The taxBand table

Tax band low boundary	Tax band high boundary	Percentage
0	10,000	0%
10,000	30,000	10%
30,000	-	15%

Consider the tax payers shown in Table 5-22.

Table 5-22. The earnings table

Name	Earnings
Corbett	5,000
Barker	10,100
Cleese	30,100

Corbett earns $5,000 and pays nothing; his earnings lie in the zero rated band.

Barker earns $10,100. He should pay 10 percent of the $100 that he earned over the $10,000 limit. His bill will be $10.

Cleese earns $30,100 and pays 10 percent on the $20,000 from $10,000 to $30,000 but then has to pay 15 percent of that $100 over the $30,000 limit. His bill will be $200 + $15 = $215.

To write SQL to perform this calculation you can establish which of the tax bands applies to which person. A high earner will be paying in several bands. A tax band is relevant if the earnings are above the low end of the band:

mysql> SELECT *
 -> FROM earning JOIN taxBand ON earning.amnt > taxBand.lowEnd;
+---------+----------+----------+---------------+-------+
| name | amnt | lowEnd | highEnd | rate |
+---------+----------+----------+---------------+-------+
| Corbett | 5000.00 | 0.00 | 10000.00 | 0.00 |
| Barker | 10100.00 | 0.00 | 10000.00 | 0.00 |
| Barker | 10100.00 | 10000.00 | 30000.00 | 10.00 |
| Cleese | 30100.00 | 0.00 | 10000.00 | 0.00 |
| Cleese | 30100.00 | 10000.00 | 30000.00 | 10.00 |
| Cleese | 30100.00 | 30000.00 | 9999999999.99 | 15.00 |
+---------+----------+----------+---------------+-------+

The exposure to the tax band is the amount earned over the low end. However, that exposure is never more than the width of the band.

Barker should pay tax on $10,100 $10,000 = $100 in the 10 percent tax band.

Cleese should pay tax on $20,000 in the 10 percent band, even though he earned $100 more than the $30,000 high end of the band. He will pay 15 percent on that last $100. You can use CASE to do this, but the query is more readable using the LEAST function:

mysql> SELECT name,amnt,lowEnd,
 -> LEAST(amnt-lowEnd,highEnd-lowEnd) exposure,
 -> rate
 -> FROM earning JOIN taxBand ON earning.amnt > taxBand.lowEnd
 -> ORDER BY amnt,lowEnd;
+---------+----------+----------+----------+-------+
| name | amnt | lowEnd | exposure | rate |
+---------+----------+----------+----------+-------+
| Corbett | 5000.00 | 0.00 | 5000.00 | 0.00 |
| Barker | 10100.00 | 0.00 | 10000.00 | 0.00 |
| Barker | 10100.00 | 10000.00 | 100.00 | 10.00 |
| Cleese | 30100.00 | 0.00 | 10000.00 | 0.00 |
| Cleese | 30100.00 | 10000.00 | 20000.00 | 10.00 |
| Cleese | 30100.00 | 30000.00 | 100.00 | 15.00 |
+---------+----------+----------+----------+-------+

All you need to do now is apply the rate to the exposure for each band and sum the contribution for each taxpayer. You can do this with a single SELECT statement, but a nested SELECT is neater:

mysql> SELECT name, ROUND(SUM(exposure*rate/100),2) taxDue FROM
 LEAST(amnt-lowEnd,highEnd-lowEnd) exposure,
 -> (SELECT name,amnt,lowEnd,
 -> LEAST(amnt-lowEnd,highEnd-lowEnd) exposure,
 -> rate
 -> FROM earning JOIN taxBand ON earning.amnt > taxBand.lowEnd
 -> ) t
 -> GROUP BY name
 -> ORDER BY taxDue;
+---------+---------+
| name | taxDue |
+---------+---------+
| Corbett | 0.00 |
| Barker | 10.00 |
| Cleese | 2015.00 |
+---------+---------+

 

5.16.1. Working Without LEAST

The LEAST function returns the smallest of the values given. Oracle supports LEAST and so does MySQL, but it is not part of the standard. If you are using a system without this function you can use a CASE statement:

mysql> SELECT name, ROUND(SUM((CASE WHEN amnt
 -> ELSE highEnd-lowEnd END)*rate/100
 -> ),2) taxDue
 -> FROM earning JOIN taxBand ON earning.amnt > taxBand.lowEnd
 -> GROUP BY name;
+---------+---------+
| name | taxDue |
+---------+---------+
| Corbett | 0.00 |
| Barker | 10.00 |
| Cleese | 2015.00 |
+---------+---------+

Hack 40 Calculate Rank