How to write sql statement to calculate totals based on groups from another table?

Question

I need to calculate the percentage of the cumulative value. The interest rate applied to each value in each row depends on the rates taken from another table. The calculation of the interest rate should be carried out in stages, since the tax can be calculated on the basis of profit.

for example: Salary = 1000
600 * 10% [The first 600 US dollars calculated at a lower tax rate]
400 * 30% [The remaining amount calculated at a higher tax rate]

So, I tried to get this to work, but I can't figure it out. The DBA left and he was transferred to me. In most SQL, I'm fine, but I'm not sure how to approach this problem or what I should look for in google, so apologies are a simple search, just call me by the URL, I'll try and work it out myself!

In any case, the following is an example of a data table format (#v) and an example of a range table (#tiers) and how I got so far. I need a new column that calculates "cval" with the correct interest rate levels, as I 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

Assuming the Amount column in the Tiers table is 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 

Answer 2

If your actual logic has a lot more rules than this, you better write it in a procedural language like PL / SQL or T-SQL, because there is a possibility ... other applications can use this logic using .. let's say .. * get_tax_for_pay (i_pay) * or something like that.

But if that’s all you need, then the below SQL should be good enough.

Tested in Oracle, because at the moment I do not have access to the SQL server. If you have any questions, 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 calculation...

• You are only interested in taxation of amounts in which a particular payment crosses a predetermined threshold for the tax scale. Example .. $ 1000 is not taxed in the 3k-5k tax group.

• In cases where the amount crosses the threshold, you will charge a minimum of a) (max-min) at that threshold b) (minimum payment threshold) so. for a fee of $ 5500, you will only pay $ 1000 in a tax group of $ 1000-2000. for payment, if 1200, you pay only $ 200 in a tax scale of $ 1000-2000.

• If you do not have minimum and maximum values ​​in different columns, you can use the input / delay functions or self-join to get both of them on the same line as in my test table. If you do not have a maximum value for the last range, use the NVL or the corresponding function to assign a really large value to determine the range. (or code for zeros :))