last salary drawn record for employees

  • i have a salary database with salary date, employee code and other info. now what i want last salary drawn date for all employees, i am using sub query which is geting very slow is there any other option

  • Hi Uzwa ul hague

    can u send us the query u r using

  • give us the column names and the data types. so that query can be written for ur req.

    Cheers,
    Sugeshkumar Rajendran
    SQL Server MVP
    http://sugeshkr.blogspot.com

  • salarydate is datetime,

    employee code is varchar

    income/tax figures are float

  • if employees can not have salary reductions:

    select [employee code], max(income),max(tax)

    from employeesalaryhistory

    if emplyees can have salary reductions, i'm pretty sure you need a subquery:

    select [employee code], income, tax

    from employeesalaryhistory a inner join (select [employee code], max(salarydate) msalarydate group by  [employee code])  on  a.[employee code]=b.[employeecode] and a.salarydate=b.msalarydate

  • You will never run out of options on this one, you can use EXISTS / NOT EXISTS for these as well.

    SELECT

     H.[Employee Code]

     , H.Income

     , H.Tax

    FROM

     EmployeeSalaryHistory H

    WHERE

     NOT EXISTS(SELECT H2.[Employee Code] FROM EmployeeSalaryHistory H2

      WHERE H2.[Employee Code] = H.[Employee Code] AND H2.SalaryDate > H.SalaryDate)

    You have to watch out - this will serialize your query and can make it slow.  You could also create a function that returns the latest date for a specified employee code to make what you are doing more clear.

  • Thank you guys!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply