Alejandro Mesa
3/19/2007 8:03:00 PM
For SQL Server 2005, you can try:
;with cte
as
(
select
Gender,
Salary,
row_number() over(partition by EmpID order by [Salary Date] desc) as rn
from
dbo.t1
)
select
Gender,
count(*) as cnt,
avg(Salary) as avg_Salary
from
cte
where
rn = 1
group by
Gender;
AMB
"Alejandro Mesa" wrote:
> Try:
>
> select
> Gender,
> count(*) as cnt,
> avg(salary) as avg_salary
> from
> dbo.t1 as a
> where
> [Salary Date] = (select top 1 b.[Salary Date] from dbo.t1 as b where
> b.EmpID = a.EmpID order by b.[Salary Date] desc)
> group by
> Gender
> go
>
>
> AMB
>
>
> "rodchar" wrote:
>
> > hey all,
> > I have the following table in which i need to get a count, percentage,
> > average salary.
> >
> > EmpID,Salary,Salary Date,Gender
> > 1,1.00,1/1/07,M
> > 1,2.00,3/1/07,M
> > 2,1.00,3/1/07,F
> > 3,1.00,3/1/07,M
> >
> > The problem i have is the employee that has 2 salary records. i only need
> > the most recent salary record for an employee to get a count of
> > males/females, percentage, avg salary. what's the best way to get the
> > information i need without including that 1 duplicate record?
> >
> > thanks,
> > rodchar