January 31, 2019 at 2:35 pm
Hi guys,
I am stuck with a task & 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 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. 🙂
January 31, 2019 at 3:24 pm
I'd 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!
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
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
January 1, 2020 at 12:16 pm
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 for 2019
VALUES(20711, 0.3665),(68507, 0.3810),(1000000,0.5175)
-- Values for 2020
-- VALUES(20711, 0.3735),(68507, 0.3735),(1000000,0.5175)
-- Values for 2021
-- VALUES(20711, 0.3710),(68507, 0.3710),(1000000,0.5175)
These are the tax brackets for the Netherlands. They are based on the Euro, but they are about the same value as the US dollar.
As can be seen taxes from 2021 (compared to 2020) go down for everybody.
As can be seen taxes from 2020 (compared to 2019) go down for a number of people. But up for lower incomes.
And the number of 'slabs' (???) is reduced between 2019 and 2020. First two slabs are combined.
So taxes do not always go up. (One shouldn't limit yourself to only the US and only a limited amount of time 🙂 )
As can be seen taxes in the Netherlands are higher than in the US. (Taxes on income)
Thanks for your code and as can be seen inserting different numbers, the system can be used in other parts of the world as wel. This shows that the code is more universal (and still simple) so it is better. (I do realize that this is a response to an older response, but maybe tax questions come up this time of year again).
Thanks,
Ben
a
January 1, 2020 at 11:53 pm
If you add just one more column to the table, you can avoid the Triangular Joins that are created to calculate things that will not change for a given year. And another column and the table can hold multiple years worth of tax rates and be just as effective.
Here's the code I used to build the table with the two extra columns...
--=====================================================================================================================
-- Create the tax bracket table.
-- Note that this should be a permanent table. We''re creating it as a Temp Table just for demo purposes.
-- Be cautious because we drop this table to make reruns in SSMS easier.
--=====================================================================================================================
--===== If the table exists, drop the table to make reruns in SSMS easier.
DROP TABLE IF EXISTS #TaxBracket
;
GO
--===== Create the table
CREATE TABLE #TaxBracket
(
TaxYear INT NOT NULL
,IncomeCutoffLo DECIMAL(19,4) NOT NULL -- 15 digits to the left of the decimal point should handle anything.
,IncomeCutoffHi DECIMAL(19,4) NOT NULL -- 15 digits to the left of the decimal point should handle anything.
,BracketTaxRate DECIMAL( 5,4) NOT NULL
--,IncomeBracketRange DECIMAL(19,4) NOT NULL -- For sanity checks only. Can remove for Prod or leave.
--,MaxBracketRangeTax DECIMAL(19,4) NOT NULL -- For sanity checks only. Can remove for Prod or leave.
--,CumeMaxBracketRangeTax DECIMAL(19,4) NOT NULL -- For sanity checks only. Can remove for Prod or leave.
,PrevCumeMaxBracketRangeTax DECIMAL(19,4) NOT NULL
,CONSTRAINT PK_TaxBracket PRIMARY KEY CLUSTERED (IncomeCutoffLo,TaxYear)
,CONSTRAINT AK_TaxBracket UNIQUE (IncomeCutoffHi,TaxYear)
)
;
--===== Populate the table with new info for the Tax Year of 2019
WITH ctePreAgg1 AS
(
SELECT v.*
,IncomeCutoffLo = LAG(v.IncomeCutoffHi,1,0) OVER (ORDER BY v.IncomeCutoffHi)
,IncomeBracketRange = (v.IncomeCutoffHi-LAG(v.IncomeCutoffHi,1,0) OVER (ORDER BY v.IncomeCutoffHi))
,MaxBracketRangeTax = (v.IncomeCutoffHi-LAG(v.IncomeCutoffHi,1,0) OVER (ORDER BY v.IncomeCutoffHi))
* v.BracketTaxRate
FROM (VALUES
(2019,14000, 0.1050)
,(2019,48000, 0.1750)
,(2019,70000, 0.3000)
,(2019,9999999.99,0.3300) --999999999999999.9999 should always be the final IncomeCutoff for this table
) v (TaxYear,IncomeCutoffHi,BracketTaxRate)
)
,
ctePreAgg2 AS
(
SELECT pa1.*
,CumeMaxBracketRangeTax = SUM(pa1.MaxBracketRangeTax) OVER (ORDER BY pa1.IncomeCutoffHi)
FROM ctePreAgg1 pa1
)
INSERT INTO #TaxBracket WITH (TABLOCK)
(
TaxYear
,IncomeCutoffLo
,IncomeCutoffHi
,BracketTaxRate
--,IncomeBracketRange -- For sanity checks only. Can remove for Prod or leave.
--,MaxBracketRangeTax -- For sanity checks only. Can remove for Prod or leave.
--,CumeMaxBracketRangeTax -- For sanity checks only. Can remove for Prod or leave.
,PrevCumeMaxBracketRangeTax
)
SELECT TaxYear
,IncomeCutoffLo
,IncomeCutoffHi
,BracketTaxRate
--,IncomeBracketRange -- For sanity checks only. Can remove for Prod or leave.
--,MaxBracketRangeTax -- For sanity checks only. Can remove for Prod or leave.
--,CumeMaxBracketRangeTax -- For sanity checks only. Can remove for Prod or leave.
,PrevCumeMaxBracketRangeTax = LAG(pa2.CumeMaxBracketRangeTax,1,0.00) OVER (ORDER BY pa2.IncomeCutoffHi)
FROM ctePreAgg2 pa2
;
--===== Let''s see what we''ve created
SELECT * FROM #TaxBracket
;
Here's some code to build a 10 million row test table (not to worry... it takes less than 5 seconds on a decent system)
DROP TABLE #TestData
;
SELECT TOP (10000000)
ID = IDENTITY(INT,1,1)
,TaxYear = 2019
,Income = CONVERT(DECIMAL(19,4),RAND(CHECKSUM(NEWID()))*100000)
INTO #TestData
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
;
... and here's the code that uses the two new columns. I frequently get beat by others' follow-up improvements but this code uses 10 times less CPU, more than 3733 times less in Logical Reads, and runs in almost 10 times less Duration than the code that does the accidental Triangular Joins. It also makes the code a shedload easier to write and read. It's all due to just precalculating that one extra column.
SELECT td.ID,td.Income,TaxDue = (td.Income-tb.IncomeCutoffLo) * tb.BracketTaxRate + tb.PrevCumeMaxBracketRangeTax
--,* -- For sanity checks only. Can remove for Prod or leave.
--,(@Income-IncomeCutoffLo) -- For sanity checks only. Can remove for Prod or leave.
--,(@Income-IncomeCutoffLo) * BracketTaxRate -- For sanity checks only. Can remove for Prod or leave.
--,PrevCumeMaxBracketRangeTax -- For sanity checks only. Can remove for Prod or leave.
INTO #Results
FROM #TaxBracket tb
JOIN #TestData td
ON tb.TaxYear = td.TaxYear
AND td.Income > tb.IncomeCutoffLo
AND td.Income <= tb.IncomeCutoffHI
;
p.s. I didn't do any rounding because I don't know what the rounding rules are. They can also vary in the U.S.A. if you round to the nearest dollar, which is allowed.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply