How to Calculate Tax in SQL?

Transact-SQL
1

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

2

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

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