CODEWITHSUNDEEP
phpmysqlsubquery
WhiskeyMike
WhiskeyMike

Reputation: 43

MySQL Subquery for max employee hours

I've got a database table called "hours" which has the following fields: "employee_id" (INT), "time_on" (DATETIME), and "time_off" (DATETIME). I want to see which employee logged the most hours in a month.

I'm thinking I need a Subquery but I'm not very good with MySQL, and I cannot wrap my head around the query I need. I want to select all rows from this month with:

"... MONTH(time_on) = ".$this_month_object->format('m')."... "

Then with those rows, I want to select the "employee_id" field with the highest seconds worked using something like:

SUM(time_to_sec(TIMEDIFF(time_off, time_on)))

The last few hours have proved fruitless and I need some fresh ideas.

Upvotes: 0

Views: 76

Answers (1)

Kermit
Kermit

Reputation: 34063

Something like this?

SELECT employee_id, SUM(TIMEDIFF(time_off, time_on)) AS diff
FROM tbl
WHERE YEAR(time_on) = 2013
    AND MONTH(time_on) = 3
GROUP BY employee_id

See the demo

Upvotes: 4

Related Questions