Get date of max hours worked

Question

Tables: Employee ( ID , Name ) EmployeeHours ( EmployeeID ,Date, Hours )

This is my requirement:

Write a sql for following results for a year EmployeeID , TotalHoursWorked , Date and Hours ( Date and Hours when Employee worked most )

I wrote the following query:

select e.id, e.name, sum(eh.hours) as totalHoursWorked, max(hours) hour
from shoplist.emp e, shoplist.empHours eh where e.id = eh.empId 
group by eh.empId;

I am able to get id, name, totalHoursWorked with maxHour but I am a bit confused how to get the date of the max hours. Can someone help me with this?

Sample Data:

Emp Table

id, name
'1','akhil'
'2','preethi'
'3','gopi'

Emp Hours Table

id, empId, hours, date
'1','1','3','2022-05-12'
'2','1','5','2022-05-11'
'3','1','4','2022-05-10'
'4','2','2','2022-05-12'
'5','2','4','2022-05-10'
'6','3','3','2022-05-09'
'7','3','5','2022-05-08'

Expected

id, name, totalHoursWorked, maxHours, maxHourDate
'1','akhil','12','5','2022-05-12'
'2','preethi','6','4','2022-05-12'
'3','gopi','8','5','2022-05-09'

Answer 1

you could use you query result in join with the original table

  select distinct eh1.empId, eh1.date, t.hours, t.name, t.totalHoursWorked 
  from shoplist.empHours eh1 inner join ( 
    select e.id, e.name, sum(eh.hours) as totalHoursWorked, max(eh.hours) hours 
    from shoplist.emp e 
    inner join shoplist.empHours eh on e.id = eh.empId group by eh.empId 
  ) t on t.id = eh1.empId AND t.hours = eh1.hours

for the same use with several dates with the same numbers of hours you couldselect the max or the minn this way

  select  eh1.empId, max(eh1.date), t.hours, t.name, t.totalHoursWorked 
  from shoplist.empHours eh1 inner join ( 
    select e.id, e.name, sum(eh.hours) as totalHoursWorked, max(eh.hours) hours 
    from shoplist.emp e 
    inner join shoplist.empHours eh on e.id = eh.empId group by eh.empId 
  ) t on t.id = eh1.empId AND t.hours = eh1.hours
  group by eh1.empId