CASE WHEN to calculate tax on a tier based tax system

Question

I have a table with user incomes and i wish to calculate their income tax percentage based on that income. The issue is that the tax rate is different for each bracket e.g.:

MinLimit| MaxLimit| TaxRate
0       | 14000   | 10.50
14001   | 48000   | 17.50
48001   | 70000   | 30.00
70001   | 1000000 | 33.00

So if the income of 1 person is 49,000 then they would be taxed as follows:

14000 * 0.1050 = 1470
34000 * 0.1750 = 5950 (34,000 is income between 14k -48k)
1000  * 0.30   = 300  (1000 is remaining income)
total = 1470 + 5950 + 300 = 7720

I am running on SQL Server 2017 Express. I have tried running a chained CASE-WHEN statement i.e.

CASE WHEN
     THEN

     WHEN
     THEN 
and so on...

but I can figure out how to add the logic of subtracting the remaining amount. Please find my code below.

SELECT 'emp_name' AS 'Director', 
SUM(ABS([Transaction Amount])) AS 'INCOME',
CASE WHEN (SUM(ABS([Transaction Amount])) < 14000)
     THEN ((SUM(ABS([Transaction Amount])) - 14000) * 0.1050)

     WHEN (SUM(ABS([Transaction Amount])) > 14000 and (SUM(ABS([Transaction Amount])) < 48001))
     THEN (((SUM(ABS([Transaction Amount])) - 14000) * 0.1050) - 48000) * 0.1750 end AS 'Income Tax'
FROM Transactions

EDIT 1: Input Data:

Transaction Type| PAYEE  | Transaction Amount
DEBIT           | DEBIT  | -184.00
CREDIT          | CREDIT | 4000.00
...

Output Data:

Director | INCOME  | Income Tax
emp_name | 45100.00| NULL

Please let me know where I am going wrong or if my thinking is incorrect.

Answer 1

A correlated subquery may be the simplest to read and understand:

declare @t table (MinLimitExclusive int, MaxLimitInclusive int, TaxRate decimal(5,2))
insert into @t(MinLimitExclusive,MaxLimitInclusive,TaxRate) values
(0    ,14000  , 10.50),
(14000,48000  , 17.50),
(48000,70000  , 30.00),
(70000,1000000, 33.00)

declare @transactions table (Income decimal(10,2))
insert into @transactions (Income) values (49000)

select
    (Income - MinLimitExclusive) * TaxRate / 100 +
    (select SUM((rates2.MaxLimitInclusive - rates2.MinLimitExclusive) * rates2.TaxRate / 100) 
    from @t rates2 where rates2.MaxLimitInclusive <= rates.MinLimitExclusive)

from
    @transactions tr
        inner join
    @t rates
        on
            tr.Income > rates.MinLimitExclusive and tr.Income <= rates.MaxLimitInclusive

It's remarkably simplified when you realise that the only maths you need to do related to the actual income is related to the bracket it actually fits in - all of the lower rate brackets, by implication, were used in their entirety so you can compute those other taxes purely from the rates table.

I've changed your rates data slightly to make the computations straightforward and not need lots of +/-1 adjustments.