# How to Calculate Tax in SQL?

Hi guys,

I am stuck with a task & not getting there.

I want to calculate income tax based on a slab based taxation system. The thing is for a specific amount say \$45000 for example, the tax would be \$6895.00 as per below :

Income Amount Income Tax

Income up to \$14000, taxed at 10.5% : \$14,000.00 = \$1470.00
Income over \$14000 and up to \$48000, taxed at 17.5% : \$31,000.00 = \$5425.00
Income over \$48000 and up to \$70000, taxed at 30% (\$0.00 in this case)
Remaining income over \$70000, taxed at 33% (\$0.00 in this case)

Total tax for \$45000 is \$6895.00

Can someone please help me on how can I implement it in SQL query? Thanks a lot in advance. One way to do this is as follows.

``````DECLARE @Income DECIMAL(18,2) = 45000;

SELECT
TotalTax = SUM( CASE WHEN c > 0 THEN c ELSE 0 END )
FROM
(
VALUES
( 0.105 * (CASE WHEN @Income > 14000 THEN 14000 ELSE @income END)  ),
( 0.175 * (CASE WHEN @Income > 48000 THEN 48000 ELSE @income END - 14000) ),
( 0.300 * (CASE WHEN @Income > 70000 THEN 70000 ELSE @income END - 48000) ),
( 0.330 * (@income - 70000 ) )
) T(c);``````

I posted mine at the other site you asked on, I might as well post it here too :-).

As I said before, I strongly urge you to use a table-driven approach, since tax brackets and rates change:

``````DROP TABLE IF EXISTS #tax_brackets;

CREATE TABLE #tax_brackets (
income_minimum decimal(9, 2) NULL,
income decimal(9, 2) NOT NULL,
tax_rate decimal(5, 4) NOT NULL
)
INSERT INTO #tax_brackets (income, tax_rate)
VALUES(14000, 0.1050),(48000, 0.1750),(70000, 0.3000),(9999999.99,0.3300)

UPDATE tb
SET income_minimum = ISNULL(tb_min.income, 0)
--SELECT *
FROM #tax_brackets tb
OUTER APPLY (
SELECT TOP (1) tb_min.*
FROM #tax_brackets tb_min
WHERE tb_min.income < tb.income
ORDER BY tb_min.income DESC
) AS tb_min
SELECT 'just to see the tax brackets' AS msg, * FROM #tax_brackets

SELECT 'actual tax calcs' AS msg, id,
MAX(data.income) AS income, SUM(taxable_income * tax_rate) AS total_tax_due
FROM ( VALUES(1, 12000),(2, 36500),(3, 67200),(4, 112000) ) AS data(id, income)
INNER JOIN #tax_brackets tb ON data.income > tb.income_minimum
CROSS APPLY (
SELECT CASE WHEN data.income >= tb.income THEN tb.income - tb.income_minimum
ELSE data.income - income_minimum END AS taxable_income
) AS alias1
GROUP BY id``````