-
Mar 6th, 2013, 10:29 AM
#1
Thread Starter
Lively Member
Update Employee Salary
Hi,
This code is just an Example
Code:
update dbo.Employee set
Salary = Salary * 1.2 -- Increase Salary by 20%
where
Employeename = 'danial'
The salary of the Employee is 3000 per month till the month of June and then the salary is increased by 2000 and now the salary of the Employee is 5000 per month ,
and at end of the year , If I want to fetch the data of the employee ,how do I know from which date the Salary has been updated?
Till June 3000 per month
from June onwards 5000 per month
Is there a way to know that?
-
Mar 6th, 2013, 10:37 AM
#2
Re: Update Employee Salary
what is the structure of your table
-
Mar 6th, 2013, 11:16 AM
#3
Re: Update Employee Salary
You don't unless you store that information when you do the update.
-
Mar 6th, 2013, 01:36 PM
#4
Thread Starter
Lively Member
Re: Update Employee Salary
Yes I have stored all the information about Employee including his basic salary in the sql database ,so if I update the basic salary of an employee,from 3000 to 5000 the it show in m y end of the year result that from this period to that period the basic salary was 3000 and now the salary is 5000?
How exactly can I do this ,do I have to write any specific sql query?
-
Mar 6th, 2013, 01:56 PM
#5
Re: Update Employee Salary
Unless you have specifically included columns for previous salary and date of change no query will provide you with the information you need. It is the update that is crucial not the query!
As the 6-dimensional mathematics professor said to the brain surgeon, "It ain't Rocket Science!"
Reviews: "dunfiddlin likes his DataTables" - jmcilhinney
Please be aware that whilst I will read private messages (one day!) I am unlikely to reply to anything that does not contain offers of cash, fame or marriage!
-
Mar 6th, 2013, 02:13 PM
#6
Thread Starter
Lively Member
Re: Update Employee Salary
ok so I have to add the columns for previous salary in the update and date change also.
-
Mar 6th, 2013, 04:33 PM
#7
Re: Update Employee Salary
Personally... I wouldn't have done it that way.... if you need to track salary over time like that... I would have it as a new table.... not in the same table as the employee info
EmplId, SalaryRate, EffectiveDate
That will allow you to track salaries, over time for each employee
-tg
-
Mar 6th, 2013, 05:06 PM
#8
Re: Update Employee Salary
Originally Posted by techgnome
Personally... I wouldn't have done it that way.... if you need to track salary over time like that... I would have it as a new table.... not in the same table as the employee info
EmplId, SalaryRate, EffectiveDate
That will allow you to track salaries, over time for each employee
-tg
Another option would be to add a next_salary_id column to the table to accomplish the same thing.
This pattern in common to all great programmers I know: they're not experts in something as much as experts in becoming experts in something.
The best programming advice I ever got was to spend my entire career becoming educable. And I suggest you do the same.
-
Mar 6th, 2013, 06:40 PM
#9
Re: Update Employee Salary
A who, what? And how would that work?
-tg
-
Mar 6th, 2013, 11:11 PM
#10
Thread Starter
Lively Member
Re: Update Employee Salary
Adding a new column to an existing table ,how would it solve my problem ...when I update the column by new salary at the end of the month, when salary is to be made how would it work?
Or adding a new table ?I'm still confused .
-
Mar 7th, 2013, 05:42 AM
#11
Re: Update Employee Salary
The table you already have shouldn't be changed so that would work as it works now. The second table would store the changes you make to the salary so you can use that to see the salary history.
-
Mar 7th, 2013, 10:34 AM
#12
Thread Starter
Lively Member
Re: Update Employee Salary
But what I make the salary of a person for a whole year then how would I be calculating ,which field value will I use and how? ...still no Idea @&!#
-
Mar 7th, 2013, 10:56 AM
#13
Re: Update Employee Salary
Is this an assignment for some class you are taking?
All the payroll systems I have ever worked with always have a PAYRATE table for an employee that tracks the START and END DATE of every type of payroll amount someone might receive.
Usually that's an "amount per check" - and the annual amount is somehow figured through whatever mechanism they have for doing HUMAN RESOURCES / PERSONNEL.
You ask "make the salary of a person for a whole year" - what exactly does that mean? Is the amount you store now an ANNUAL or PER CHECK amount (like weekly or biweekly or monthly amount??)
Do you need to annualize the salary for the year for TAX RATE purposes??
-
Mar 7th, 2013, 10:59 AM
#14
Re: Update Employee Salary
simple visualization is
table employee_Data
employeeName varchar(25)
employee_code int primary Key,
PresentPay int ,
LastIncrement date
table employee_pay
employee_Code int foreign key ,
Pay_month date ,
incrementRate float
now here
(1) once the employee pay changed you can change the employee present pay in top table
(2) you can edit the employee last increment took date in the first table , so 6 month once i,e when ever the employee given the increment
(3) in the bottom table update the increment rate applicable for the month
all the above 3 things are in your control, you can change it from the front end when ever y need as per the logic
now the salary is some thing like, this is one visualization only,
SELECT employeedata.employeename,employeedata.presentpay,employeepay.incrementrate,(employeedata.presentpay ) + (employeedata.presentpay * employeepay.incrementrate) as total_pay from employeedata left join employeepay on employeedata.employeecode = employeepay.employeecode where employeepay.pay_month = your_choice ;
-
Mar 7th, 2013, 11:01 AM
#15
Re: Update Employee Salary
recommend not using FLOAT for any fields here - it's inaccurate for financial calculations.
And you should also always ROUND() values after multiplication - can't pay half a penny...
-
Mar 7th, 2013, 11:06 AM
#16
Re: Update Employee Salary
ha thanks i took your valid advise
-
Mar 8th, 2013, 01:53 AM
#17
Thread Starter
Lively Member
Re: Update Employee Salary
* szlamany*
This is no school project ,I'm learning Vb.net as a language and making a project like a payroll system, so I can cover as much as I can(learning),and in coming time when it is ready hoping that someone might be interested in for.(Just a long shot),and those calculations are on monthly basis (Monthly pay),and after a year those figures can be use for TAX Purpose as well.
If a person's pay increases from 3000 to 5000 in between a financial year ,just want to know how to track it,Now I have only Basic salary as a field in my employee data table,I guess I have to make changes to the tables as suggested by *make me rain* and you.I will also make a new table as employee pay and can track changes made in this table only.(I hope it will work).
Can I use long int as Data Type for Basic salary field,that can store as big figure like 1,00,000?or even more .
*make me rain*
In Employee data table you suggested
PresentPay int ,
LastIncrement date
So the first change in salary I have to edit in this table....(Change from 3000 to 5000)
and what is lastincrement field for?
and in the second table
Pay_month date ,
incrementRate float
In this table in the incrementrate field the diffrence between 3000 and 5000 that is 2000 will be there?
Still a lot of confusion.sorry
-
Mar 8th, 2013, 09:33 AM
#18
Re: Update Employee Salary
always the salary is a percentage of increase to some pay amount
for example
in table 1
group of data entry operators say for example their designation is clerks_scale 1
their pay at the recruitment grade will be $5000
and last increment date will be the (initially) appointment date ( this date is for reckoning the 6 month for the next increment date )
here very well u can add one more field as designation if u need it
in second table
you will enter the pay month & basic pay ( this is required to query the pay drawn on any month and this pay will change 6 months once duly adding the increment it is just a fine logic of ur requirement)
increment rate will be say for example 10%
in your report the the payment will be displayed as i mentioned in the query example
the table structure needs to be normalized neatly on a paper, & this is the foundation of the data base system
Last edited by make me rain; Mar 8th, 2013 at 09:37 AM.
Reason: spell mistake
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|