Results 1 to 18 of 18

Thread: Update Employee Salary

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Sep 2012
    Posts
    119

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

  2. #2
    PowerPoster make me rain's Avatar
    Join Date
    Sep 2008
    Location
    india/Hubli
    Posts
    2,208

    Re: Update Employee Salary

    what is the structure of your table
    The averted nuclear war
    My notes:

    PrOtect your PC. MSDN Functions .OOP LINUX forum
    .LINQ LINQ videous
    If some one helps you please rate them with out fail , forum doesn't expects any thing other than this

  3. #3
    I'm about to be a PowerPoster! Joacim Andersson's Avatar
    Join Date
    Jan 1999
    Location
    Sweden
    Posts
    14,649

    Re: Update Employee Salary

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

  4. #4

    Thread Starter
    Lively Member
    Join Date
    Sep 2012
    Posts
    119

    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?

  5. #5
    PowerPoster dunfiddlin's Avatar
    Join Date
    Jun 2012
    Posts
    8,245

    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!

  6. #6

    Thread Starter
    Lively Member
    Join Date
    Sep 2012
    Posts
    119

    Re: Update Employee Salary

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

  7. #7
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  8. #8
    Frenzied Member MattP's Avatar
    Join Date
    Dec 2008
    Location
    WY
    Posts
    1,227

    Re: Update Employee Salary

    Quote Originally Posted by techgnome View Post
    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.

  9. #9
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: Update Employee Salary

    A who, what? And how would that work?


    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  10. #10

    Thread Starter
    Lively Member
    Join Date
    Sep 2012
    Posts
    119

    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 .

  11. #11
    I'm about to be a PowerPoster! Joacim Andersson's Avatar
    Join Date
    Jan 1999
    Location
    Sweden
    Posts
    14,649

    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.

  12. #12

    Thread Starter
    Lively Member
    Join Date
    Sep 2012
    Posts
    119

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

  13. #13
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

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

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  14. #14
    PowerPoster make me rain's Avatar
    Join Date
    Sep 2008
    Location
    india/Hubli
    Posts
    2,208

    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 ;
    The averted nuclear war
    My notes:

    PrOtect your PC. MSDN Functions .OOP LINUX forum
    .LINQ LINQ videous
    If some one helps you please rate them with out fail , forum doesn't expects any thing other than this

  15. #15
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

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

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  16. #16
    PowerPoster make me rain's Avatar
    Join Date
    Sep 2008
    Location
    india/Hubli
    Posts
    2,208

    Re: Update Employee Salary

    ha thanks i took your valid advise
    The averted nuclear war
    My notes:

    PrOtect your PC. MSDN Functions .OOP LINUX forum
    .LINQ LINQ videous
    If some one helps you please rate them with out fail , forum doesn't expects any thing other than this

  17. #17

    Thread Starter
    Lively Member
    Join Date
    Sep 2012
    Posts
    119

    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

  18. #18
    PowerPoster make me rain's Avatar
    Join Date
    Sep 2008
    Location
    india/Hubli
    Posts
    2,208

    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
    The averted nuclear war
    My notes:

    PrOtect your PC. MSDN Functions .OOP LINUX forum
    .LINQ LINQ videous
    If some one helps you please rate them with out fail , forum doesn't expects any thing other than this

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width