Retreive the top 5 salaries each month for 1 year

[itechC]

Hello,

I am looking to write a query that would give me the top 5 salaries each month within the past year. Does anyone have any ideas on how to do this.

Thanks

 

[jrbarnett]

What is the structure of your tables containing the salary information? we can't help you without it.

John

 

[itechC]

The structure is as follows.

EmpId
EmpName
Bonus_payout
Date

 

[PHV]

Provided that you want the 5 greater Bonus_payout per month within the past year, here a starting point:

SELECT Format(A.Date,'yyyy-mm') AS [Month],A.EmpId,A.EmpName,SUM(A.Bonus_payout) AS Bonus
FROM yourTable A
WHERE Year(A.Date)=Year(Now())-1
GROUP BY Format(A.Date,'yyyy-mm'),A.EmpId,A.EmpName
HAVING SUM(A.Bonus_payout) IN (
  SELECT TOP 5 SUM(Bonus_payout) FROM yourTable
  WHERE Format([Date],'yyyy-mm')=Format(A.Date,'yyyy-mm')
  GROUP BY EmpId ORDER BY 1 DESC)
ORDER BY 1, 4 DESC

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886