Welcome Guest ( Log In | Register )

Outline · [ Standard ] · Linear+

 Generate EPF Table

views
     
TSaxsnoone
post Jan 20 2010, 09:44 AM, updated 15y ago

Getting Started
**
Junior Member
56 posts

Joined: Oct 2004
From: Nowhere


Any idea of how to generate EPF table. I mean the formula.
The table is from KWSP

Here is the example of that table:

Employer Share : 16%
Employee Share : 11%

CODE

+---------+-------------+-------------+-----------------------+
BoundID Lower Bound Upper Bound Employer Contribution
+---------+-------------+-------------+-----------------------+
      1 10.01       20.00       4                    
      2 20.01       40.00       7                    
      3 40.01       60.00       10                    
      4 60.01       80.00       13                    
      5 80.01       100.00       16                    
      6 100.01       120.00       20                    
      7 120.01       140.00       23                    
      8 140.01       160.00       26                    
+---------+-------------+-------------+-----------------------+
8 rows in set (0.00 sec)



So, the user shouldn't add manually one by one. For at least 200 Bounds.. sweat.gif
So, generating that table is usefull.

This post has been edited by axsnoone: Jan 20 2010, 09:58 AM
dopodplaya
post Jan 20 2010, 10:49 AM

Look at all my stars!!
*******
Senior Member
2,280 posts

Joined: Jun 2006
you can use stored procedure for that purpose.

unfortunately, I can't give you any example since database is not my forte.

For MySQL - it is explained here - Stored Procedures in MySQL 5.0

Basic "looping" can be achieve in the BEGIN and END block - BEGIN ... END Compound Statement Syntax

This post has been edited by dopodplaya: Jan 20 2010, 10:52 AM
dkk
post Jan 20 2010, 12:17 PM

10k Club
Group Icon
Elite
11,400 posts

Joined: Jan 2003
Is this for educational reason or you actually intend to implement this in a real payroll system? If the latter, I'll simply make it up on a spreadsheet (much easier), then import the whole thing into SQL.

If you need to generate it in SLQ, first you fill up BoundID from 1 to 200. Then the other columns is just a formula referencing BoundID. You'll need a few IF() statements, because the step between each row differs, switching from $20 to $100. BTW, you missed up the first $10 step.
cute_boboi
post Jan 20 2010, 06:59 PM

° 忍 °
*******
Senior Member
6,416 posts

Joined: Nov 2004
From: [Latitude-N3°9'25"] [Longitude-E101°42'45"]


Copy and paste the PDF text into Excel. Fix all the tabs that run haywire.

Then prepare the data, into formats that suit the upload into DB table, i.e. either
1) bulk-copy
2) copy-paste through designer window
3) generate insert sql statements

To retrieve the proper contribution based on salary amount,
CODE
select <contribution> from <table>
where @salary_amount between lower_bound and upper_bound

Improvise the SQL to get the contribution that you want.

dkk
post Jan 20 2010, 07:44 PM

10k Club
Group Icon
Elite
11,400 posts

Joined: Jan 2003
Where did you get the employer = 16%, employee = 11%? The link to the table shows employer = 12%, employee = 8%.
silverhawk
post Jan 21 2010, 12:28 AM

I'm Positively Lustrous
Group Icon
Elite
4,738 posts

Joined: Jan 2003


Shouldn't there be a formula for this?
dkk
post Jan 21 2010, 03:46 PM

10k Club
Group Icon
Elite
11,400 posts

Joined: Jan 2003
QUOTE(silverhawk @ Jan 21 2010, 12:28 AM)
Shouldn't there be a formula for this?
*
Yes there is. For mysql ...

CODE

SET @salary=1504.1;
#  @salary is the only variable you input
SET @rate_employer=0.12;
SET @employer = IF( @salary<=10, 0,
 IF( @salary<=5000, CEIL(CEIL(@salary/20)*20*@rate_employer),
 IF( @salary<=20000, CEIL(CEIL(@salary/100)*100*@rate_employer),
 CEIL(CEIL(@salary)*@rate_employer)  ) ) );
SET @rate_employee=0.08;
SET @employee = IF( @salary<=10, 0,
 IF( @salary<=5000, CEIL(CEIL(@salary/20)*20*@rate_employee),
 IF( @salary<=20000, CEIL(CEIL(@salary/100)*100*@rate_employee),
 CEIL(CEIL(@salary)*@rate_employer)  ) ) );
SELECT @employer, @employee;
# this is the output


I just made it up. It's nothing official from EPF, so use at your own risk! smile.gif
TSaxsnoone
post Jan 21 2010, 04:05 PM

Getting Started
**
Junior Member
56 posts

Joined: Oct 2004
From: Nowhere


QUOTE(dkk @ Jan 20 2010, 07:44 PM)
Where did you get the employer = 16%, employee = 11%? The link to the table shows employer = 12%, employee = 8%.
*
Thats table shows employer = 12%, employee = 8%. But in my example i get it with another % of contribution instead of 12%18%.


QUOTE(silverhawk @ Jan 21 2010, 12:28 AM)
Shouldn't there be a formula for this?
*
yes.. i've made for jsp.. its just simple actually.. the number to calculate just upper.

Example,

Upper = 20,
Employer Contribution = 16%

So, 20-(20*0.84) = 20 - 16.8

In my java, i used Math.floor to round 16.8 to 16,

Result is 20-16 = 4 and control it with loop.. and generate the table. Same with EPF table link above.

Im not ready yet to use SP for Mysql.. tongue.gif

thanks to all for that idea.. thumbup.gif

 

Change to:
| Lo-Fi Version
0.0172sec    0.21    5 queries    GZIP Disabled
Time is now: 29th March 2024 - 10:31 PM