January 31, 2019 at 2:25 pm
Hi guys,
I am stuck with a task with not getting there?
I want to calculate income tax based on a taxation system. The thing is for a specific amount say $45000 for example, the tax would be $6895.00 as per below :
Income Tax Rate 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 $0.00
Remaining income over $70000, taxed at 33% $0.00 $0.00
Total $45000 $6895.00
Can someone please help me on how can I implement it in SQL query? Thanks a lot in advance. 🙂
January 31, 2019 at 7:38 pm
Duplicate post, see How-to-calculate-income-tax-for-a-slab-based-taxation-system
...
December 24, 2019 at 8:49 am
Please refer to this same duplicated post for your reference. It will strongly urge you to make this table driven rather than hard-coding any of this. If we know anything, it's that taxes always go up!
You can look at the examples and see to it.
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)
--dynamically determine the lower range for each tax bracket based on the values
--in the table, i.e. NOT hard-coded.
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) /*sample income amounts*/
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply