How do I write a sql statement to calculate totals, based on groups from another table?

Question

I need to calculate a percentage from a cumulative value. The percent rate to be applied to each value in each row is dependent on rates taken from another table. The percent rate calculation needs to happen in a tiered fashion, as tax might be calculated on earnings.

eg: Wages = 1000
600 * 10% [First $600 calculated at lower tax rate]
400 * 30% [Remaining amount calculated at higher tax rate]

So, I've been trying to get this to work, but can't sort it out. The DBA is away so it's been handed over to me. Most SQL I'm ok with, but I am not sure how to approach this issue, or what I should be searching for in google either, so apologies is this is a simple search away, please just direct me to the URL and I'll try and work it out myself!

Anyway, below is an example of the format of the data table (#v) and an example of the ranges table (#tiers), and how I have got on so far. I need a new column which has the calculation of 'cval' at the correct percent rate tiers as I've explained above.

Hope someone can help or point me in the right direction! Thanks, J.

create table #v(
id nvarchar(50),
val money,
tid int
)

insert into #v values ('a',30,1)
insert into #v values ('b',50,1)
insert into #v values ('c',10,1)
insert into #v values ('d',30,1)
insert into #v values ('e',-80,1)


create table #tiers (
tid int,
threshold money,
amount money
)

insert into #tiers values (1,0,30)
insert into #tiers values (1,40,40)
insert into #tiers values (1,100,50)


select * from
(
select v1.id, v1.tid, v1.val,sum(v2.val) cval
from #v v1
inner join #v v2 on v1.id >= v2.id
group by v1.id, v1.val, v1.tid
) a
left join
(
       select a.tid, a.id, a.threshold [lower], b.threshold [upper] from
       (
               select rank() over (order by threshold) as id, tid, threshold, amount from #tiers
       ) a
       left join
       (
               select rank() over (order by threshold) as id, tid, threshold, amount from #tiers
       ) b on a.id = b.id-1
) b on (a.cval >= lower and a.cval < upper) or (a.cval >= lower and upper is null)

Answer 1

If your actual logic has a lot more rules than this one, you are better off writing this in a procedural language like PL/SQL or T-SQL because chances are.. other applications might want to use this logic using..say.. *get_tax_for_pay(i_pay)* or something like that.

But if this is all you need, then the below SQL should be good enough.

Tested this in Oracle as I have no access to SQL server at the moment. If you have any issues post it in the comments. Notes at the end.

create table gross_pay(
  pay number);

insert into gross_pay values (1523);
insert into gross_pay values (500);
insert into gross_pay values (5600);
insert into gross_pay values (3523);
commit;

create table tax_range(
  min_pay number,
  max_pay number,
  tax_percent number);

insert into tax_range values (1000, 2000, 10);
insert into tax_range values (2000, 3000, 20);
insert into tax_range values (3000, 4000, 30);
insert into tax_range values (4000, 100000, 35);
commit;

SQL> select * from gross_pay;

       PAY
----------
      1523
       500
      5600
      3523

SQL> select * from tax_range;

   MIN_PAY    MAX_PAY TAX_PERCENT
---------- ---------- -----------
      1000       2000          10
      2000       3000          20
      3000       4000          30
      4000     100000          35

select g.pay, t.min_pay, t.max_pay, t.tax_percent,
  (g.pay-t.min_pay) diff, (t.max_pay-t.min_pay) diff2,
  (case when g.pay > t.min_pay then
        least((t.max_pay-t.min_pay),(g.pay-t.min_pay))
        else 0
    end) Taxable
  from gross_pay g, tax_range t
  order by pay, min_pay
SQL> /

       PAY    MIN_PAY    MAX_PAY TAX_PERCENT       DIFF      DIFF2    TAXABLE
---------- ---------- ---------- ----------- ---------- ---------- ----------
       500       1000       2000          10       -500       1000          0
       500       2000       3000          20      -1500       1000          0
       500       3000       4000          30      -2500       1000          0
       500       4000     100000          35      -3500      96000          0
      1523       1000       2000          10        523       1000        523
      1523       2000       3000          20       -477       1000          0
      1523       3000       4000          30      -1477       1000          0
      1523       4000     100000          35      -2477      96000          0
      3523       1000       2000          10       2523       1000       1000
      3523       2000       3000          20       1523       1000       1000
      3523       3000       4000          30        523       1000        523

       PAY    MIN_PAY    MAX_PAY TAX_PERCENT       DIFF      DIFF2    TAXABLE
---------- ---------- ---------- ----------- ---------- ---------- ----------
      3523       4000     100000          35       -477      96000          0
      5600       1000       2000          10       4600       1000       1000
      5600       2000       3000          20       3600       1000       1000
      5600       3000       4000          30       2600       1000       1000
      5600       4000     100000          35       1600      96000       1600

 select pay, sum(tax) from (
 select pay, min_pay, max_pay, tax_percent, Taxable,
        (Taxable* tax_percent/100) tax from (
 select g.pay, t.min_pay, t.max_pay, t.tax_percent,
        (g.pay-t.min_pay) diff, (t.max_pay-t.min_pay) diff2,
        (case when g.pay > t.min_pay then
              least((t.max_pay-t.min_pay),(g.pay-t.min_pay))
              else 0
         end) Taxable
   from gross_pay g, tax_range t
   order by pay, min_pay

       PAY   SUM(TAX)
---------- ----------
      1523       52.3
      3523      456.9
       500          0
      5600       1160

For the calculation...

1. You are only interested in the taxing the amounts where a particular pay crosses a given threshold for the tax bracket. Example.. 1000$ would not be taxed for in the 3k-5k tax bracket.

2. In cases where the amount crosses the threshold, you will charge the minimum of a) (max-min) in that threshold b) (pay-threshold minimum) so. for a pay of 5500, you'll only charge 1000$ in the 1000-2000$ tax bracket. for a pay if 1200, you'll only charge 200$ in the 1000-2000$ tax bracket.

3. If you do not have the min and max in different columns, you can use the lead/lag functions or a self-join to get both of them in the same row as in my test table. If you don't have a max value for the last range, use NVL or the corresponding function to assign a really large value to define the range. (or code for nulls :))

Answer 2

Assuming the Amount column in the Tiers table is supposed to be a tax rate, you can do something like:

With VData As
    (
    Select V1.id, V1.val, V1.tid, Sum(V2.val) As CVal
    From #V As V1
        Join #V As V2
            On V2.id <= V1.id
    Group By V1.id, V1.val, V1.tid
    )
    , Tiers As
    (
    Select T1.tid
        , T1.Amount
        , T1.threshold As MinThreshold
        , Min(Coalesce(T2.threshold, 2147483647)) As MaxThreshold
    From #tiers As T1
        Left Join #tiers As T2
            On T2.threshold > T1.threshold
    Group By T1.tid, T1.Amount, T1.threshold
    )
Select V.id, V.val, V.tid, V.CVal
    , Sum(
        Case
        When CVal > T.MaxThreshold Then T.Amount / 100.00 * T.MaxThreshold
        When CVal >= T.MinThreshold Then T.Amount / 100.00 * (V.CVal - T.MinThreshold)
        End) As TotalTax
From VData As V
    Join Tiers As T
        On T.tid = V.tid
Group By V.id, V.val, V.tid, V.CVal