How to get Highest Sales Person in Month for Monthly Reward

Question

Dear All,

 

I have a SQL question not able to get exact record.

How to get Monthly Highest Sales Person in given record.

Please tell me where I am wrong.

 

Employee Table:

  

Sales Table: 

 

Expected Result:

 

 

Tried Query:

 

With Temp AS(

Select e.empID,SUm(s.Sale) as Sale,s.monthname

From tblEMp e inner join tblSales s on e.empID=s.empID

Group by s.monthname,e.empiD)

Select Distinct(Sale),empID,monthName from Temp Where Sale in (Select Max(Sale) from Temp Group by monthName)

Group by monthName,empID,sale

order by monthname