January 20, 2007 at 1:52 am
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
January 20, 2007 at 3:32 am
Hi Uzwa ul hague
can u send us the query u r using
January 20, 2007 at 7:31 am
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
January 22, 2007 at 5:06 am
salarydate is datetime,
employee code is varchar
income/tax figures are float
January 22, 2007 at 6:29 am
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
January 22, 2007 at 1:25 pm
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.
January 23, 2007 at 12:45 am
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