Welcome Guest ( Log In | Register )

 
RSS feedBump TopicReply to this topicStart new topicStart Poll

Outline · [ Standard ] · Linear+

> Generate EPF Table

axsnoone
post Jan 20 2010, 09:44 AM


Getting Started
**
Group: Junior Member
Posts: 56

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
User is offlineProfile CardPM
Go to the top of the page
+Quote Post
dopodplaya
post Jan 20 2010, 10:49 AM


Look at all my stars!!
*******
Group: Senior Member
Posts: 2,280

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
User is offlineProfile CardPM
Go to the top of the page
+Quote Post
dkk
post Jan 20 2010, 12:17 PM


Look at all my stars!!
Group Icon
Group: Elite
Posts: 9,587

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.
User is offlineProfile CardPM
Go to the top of the page
+Quote Post
cute_boboi
post Jan 20 2010, 06:59 PM


° 忍 °
*******
Group: Senior Member
Posts: 5,303

Joined: Nov 2004
From: [Latitude-N3°9'25"] [Longtitude-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.

User is offlineProfile CardPM
Go to the top of the page
+Quote Post
dkk
post Jan 20 2010, 07:44 PM


Look at all my stars!!
Group Icon
Group: Elite
Posts: 9,587

Joined: Jan 2003
Where did you get the employer = 16%, employee = 11%? The link to the table shows employer = 12%, employee = 8%.
User is offlineProfile CardPM
Go to the top of the page
+Quote Post
silverhawk
post Jan 21 2010, 12:28 AM


I'm Positively Lustrous
Group Icon
Codemasters, Cupid's Corner
Group: Elite
Posts: 3,742

Joined: Jan 2003


Shouldn't there be a formula for this?
User is offlineProfile CardPM
Go to the top of the page
+Quote Post
dkk
post Jan 21 2010, 03:46 PM


Look at all my stars!!
Group Icon
Group: Elite
Posts: 9,587

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
User is offlineProfile CardPM
Go to the top of the page
+Quote Post
axsnoone
post Jan 21 2010, 04:05 PM


Getting Started
**
Group: Junior Member
Posts: 56

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
User is offlineProfile CardPM
Go to the top of the page
+Quote Post

Bump TopicReply to this topicTopic OptionsStart new topic
 

Switch to:
| Lo-Fi Version
0.0641sec    2.78    5 queries    GZIP Disabled
Time is now: 23rd April 2014 - 05:01 PM