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?

what is the structure of your table

You don't unless you store that information when you do the update.

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?

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!

ok so I have to add the columns for previous salary in the update and date change also.

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

Quote:

---

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.

A who, what? And how would that work?


-tg

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 .

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.

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 @&!#

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??

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,

Quote:

---

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 ;

---

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...

ha :D thanks i took your valid advise

* 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

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