How to make a case query in Base from 2 tables

[ZeoZordon]

Hi

I have 2 tables that I want to run a complex query from but I do not know if my SQL is correct or if I can even do the query as I want it to run.

Here is my query screenshot so you can see the 2 tables I have in

Now for this query to work as intended I need the following to happen:

CASE
WHEN “Gross Salary” * 12 <= “TT1” THEN “Gross Salary” * 12 * “TB1” – “R” / 12
WHEN “Gross Salary” * 12 < “TT2” THEN (“Gross Salary” * 12 – “TT1”) * “TB2” + “TA2” – “R” / 12
WHEN “Gross Salary” * 12 < “TT3” THEN (“Gross Salary” * 12 – “TT2”) * “TB3” + “TA3” – “R” / 12
WHEN “Gross Salary” * 12 < “TT4” THEN (“Gross Salary” * 12 – “TT3”) * “TB4” + “TA4” – “R” / 12
WHEN “Gross Salary” * 12 < “TT5” THEN (“Gross Salary” * 12 – “TT4”) * “TB5” + “TA5” – “R” / 12
WHEN “Gross Salary” * 12 > “TT5” THEN (“Gross Salary” * 12 - “TT6”) * “TB6”+ “TA6” – “R” / 12
ELSE 0
END

I am not a pro just a novice user but I need this query to work out an amount for me.
Basicly it should get the “Gross Salary” amount from Table March and then look at the 6 conditions and do the corresponding equation to get the amount I need and if Gross salary does not meet 1 of the 6 conditions then the amount will be 0.
This is for HSQLDB embeded.
Any help will be appreciated.

[Ratslinger]

Hello,

It appears you are attempting to calculate taxes - this can present legal issues. Regardless, you specify:

Now for this query to work as intended I need the following to happen:

If in fact the SQL is incorrect (and it appears to be in a number of ways), how does one determine this without you presenting what items/fields represent and exactly what the calculation is that you are attempting?

Some probable errors already - No condition for Gross Salary * 12 = TT5; No consistency in each level for user of Gross Salary ( ie: should it be < “TT2” THEN ((“Gross Salary” * 12) – “TT1”)).

This is just a cursory look.

[ZeoZordon]

Hi Ratslinger

yes this is to work out taxes, I have set up a small but nice little payroll type base database that is working awesome for our small company but I have to sit and work out the monthly taxes for each employee manually still every month so I want to make a query that can do it for me.
Now the table called Tax Tables is what I get from our Government every year so I will just change this table as they change our taxes every year, Herewith is how the tax table looks like and also what each item/field is for:

Tax Tables			
	 Min Yearly 	 Max Yearly 	        Yearly Tax 	 Tax % 
	 000,000.00      188000 (TT1) 	 33840 (TA1) 	 0.18 (TB1) 
	 188,001.00 	 293600 (TT2) 	 61296 (TA2) 	 0.26 (TB2) 
	 293,601.00 	 406400 (TT3) 	 96264 (TA3) 	 0.31 (TB3) 
	 406,401.00 	 550100 (TT4) 	 147996 (TA4)  0.36 (TB4) 
	 550,101.00 	 701300 (TT5) 	 206964 (TA5)  0.39 (TB5) 
	 701,301.00 	  701300(TT6) 	 206964 (TA6)  0.41 (TB6) 
				
 Rebate (R) 13,500.

[ZeoZordon]

I also changed the first question you where right I forgot to put the * 12 when I typed the query out in the question.

[Ratslinger]

Your table and math don’t match.

• Still have not accounted for Gross Salary * 12 = TT5 only less & greater

• TA1 value is never used

• Addition of TA(x) in each line appears to be in wrong level

• TT6 & TA6 should not even appear as they are totally incorrect and are never used

• With the manner written Tax %, is it .18% or 18% (logic says 18% but this is not to be guessed)

[Ratslinger]

So I was going to try to put together a simple test. Looked at your table definitions and can’t figure out why the tax table has an employee number in it. Isn’t the tax table for all employees?

Still seeing other inconsistencies. This seems OK:

WHEN “Gross Salary” * 12 <= “TT1” THEN

But then why not other levels for <= ?

Edit:

Have a rough calculation but you have not provided the formula. So please provide your calculation of tax for:

• 10000

• 20000

• 30000

• 40000

• 50000

• 60000

For example on 10000 my result is 675

Edit:

Note that these amounts are Gross Salary figures from March records (employee).

[ZeoZordon]

The Tax Table is for all employees yes you are correct, but I need to have the calculation for each employee correspond to each employees gross salary so I thought I need to have a primary key on the tax table that links up with the primary key on the tax table, like I said in the start I am not a pro user just an accountant trying to make his monthly workload less by trying to make my base program help me that I designed for our payroll.

So any advise you give me I will use, here are the examples you asked for if the amounts you gave me is the “Gross Salary” amounts in table March:

• 10000 - 10000 * 12 = 120000 so it is less than 188000(TT1) so then 10000 12 = 1200000.18(TB1) = 21600.00 - 13500(R) =8100 / 12 = 675 tax for the month of March

• 20000 - 20000 * 12 = 240000 so it is greater than 188000(TT1) and less than 293600(TT2) so then 20000*12 = 240000 - 188000(TT1) = 52000 * 0.26(TB2) = 13520 + 61296(TA2) = 74816 - 13500(R) = 61316 /12 = 5109.66 tax for the month of march

[ZeoZordon]

• 30000 - 30000 * 12 = 360000 so it is grater than 293600(TT2) and less than 406400(TT3) so then 30000*12 = 360000 - 293600(TT2) = 66400 * 0.31(TB3)= 20584 + 96264(TA3) = 116848 - 13500(R) = 103348 /12 = 8612.33 Tax for the month of March

• 40000 - 4000012 = 480000 so it is grater than 406400(TT3) and less than 550100(TT4) so then 4000012 = 480000 - 406400(TT3) = 73600 * 0.36(TB4) = 26496 + 147996(TA4) = 174492 - 13500(R) = 160992 / 12 = 13416 tax for the month of March

• 50000 - 5000012 = 600000 so it is grater than 550100(TT4) and less than 701300(TT5) so then 5000012 = 600000 - 550100(TT4) = 49900 *0.39(TB5) =19461 + 206964(TA5) = 226425 - 13500(R) = 212925/ 12 = 17743.75 Tax for the month of March

• 60000 - 6000012 = 720000 so it is greater than 701300(TT5) so then 6000012 = 720000- 701300(TT6)= 18700* 0.41(TB6) = 7667 + 206964(TA6) - 13500(R) = 201131/ 12 = R16760.91 tax for the month of March

[ZeoZordon]

Thank Ratslinger I saw now the fault in my final when statement for the math to work correctly, but still I need help in implementing it into base so any advise will help, I hope the math makes sense now with the examples I did now, updating main question with the fixed final when line

[Ratslinger]

Hello,

First let me state, it is your responsibility to verify any and all calculations. You are using any information presented here at your own risk. I do believe you need to consult legal on the calculations you have presented as it appears to me they are incorrect for the most part.

In the table the first level the maximum tax is 188000 * .18 = 33840

The second level is 293600 - 188000 = 105600 * .26 = 27456

So if one maxes out the second level it is 33840 + 27456 = 61296

In your calculations you calculate the 13520 correctly but then add in 61296 which is the TOTAL of level 1 & 2! Should only be adding in 33840. The is very obvious when you look at your totals of:

• 50000 = 17743.75 Tax for the month of March

• 60000 = 16760.91 tax for the month of March

Here you pay more when you make less. You need consultation.

Now as for SQL, there are ways to attach one table to another without creating a table for each employee. I also disagree with the design of the employee records but that is another issue.

With a Tax Table independent of the other tables (key Field is Tax year not employee number, and with calculations based upon what has previously been stated, this works:

SELECT "Employee No.",
       "Gross Salary",
       CASE
       WHEN "TaxableSalary" < "TT1" THEN ("TaxableSalary"*"TB1" - "R") / 12
       WHEN "TaxableSalary" < "TT2" THEN (("TaxableSalary" - "TT1")*"TB2" + "TA1" - "R") / 12
       WHEN "TaxableSalary" < "TT3" THEN (("TaxableSalary" - "TT2")*"TB3" + "TA2" - "R") / 12
       WHEN "TaxableSalary" < "TT4" THEN (("TaxableSalary" - "TT3")*"TB4" + "TA3" - "R") / 12
       WHEN "TaxableSalary" < "TT5" THEN (("TaxableSalary" - "TT4")*"TB5" + "TA4" - "R") / 12
      WHEN "TaxableSalary" >= "TT5" THEN (("TaxableSalary" - "TT5")*"TB6" + "TA5" - "R") / 12
         ELSE 0
       END AS "Tax"
FROM (SELECT "Employee No.",
             "Gross Salary",
             "A". *,
             "Gross Salary"*12 AS "TaxableSalary"
      FROM "March"
        LEFT JOIN (SELECT * FROM "Tax Tables" WHERE "TaxYear" = '2020') "A" ON 1 = 1)

Here is a sample using the figures previously presented:

Also you should know a separate table is not actually needed. You can do this by just inserting the appropriate table figures into the SQL. The separate table will allow you to keep multiple years of tax tables on file.

[ZeoZordon]

Hi Ratslinger

Thank you your SQL you gave me works perfect I just needed to change it as below now I can incorporate it perfectly into my payroll base

As for the calculations it is perfectly correct as per our government guidelines and formulas as I got the formulas from their tax websites and it is true if you earn 50000 a month you pay more than someone earning 60000 a month and then the person earning 70000 a month pays even more again, it all works on a tax table and the person earning 60000 scores as they are the lowest on the new tax bracket.

I am a tax consultant as I said I am an Accountant and not a programmer so all the accounting side of stuff I am 100% sure it was the SQL part I needed Help with and you sorted my question out with me just having to change a few things to fit the structure of my already used program:

LEFT JOIN ( SELECT * FROM "Tax Tables" ) "A" ON 1 = 1 )

That is the only line I had to change

[Ratslinger]

Glad this is OK for you.

As for me, I am thankful to be not needing work where those tax methods are in place. Even an extremely small monthly pay increase will cost THOUSANDS in taxes. You actually lose a LOT of money if you get a pay increase that carries you from one bracket to the next. HORRIBLE!

Example used was monthly of 15660 to 15670 - tax went from 1693.80 to 3983.87!

[ZeoZordon]

Yeah I know our tax system is not of the greatest, but once again thanks for the help this is going to make my monthly and yearly recons a huge amount easier.