Re: Query w/sum and GROUP BY for related tables?

[craig scyuli]

Thank you for your response. I've come up with a query that is working for me before seeing your post. I didn't quite understand when you said

"put the logic to record the t_out value for a given t_in in a trigger"

Here is the query I have working. I've hard coded the date for now for testing. Do you see any issues with this query, or do you recommend me to structure it like your suggestion.


SELECT CONCAT(e.last, ', ', e.first) AS name, DATE_FORMAT(p.tistamp, '%m/%d/%y') as d2,

ROUND(
(
(SELECT SUM(Case WHEN status='OUT' THEN (TIME_TO_SEC(tistamp)) ELSE 0 END )AS Outime)
-
(SELECT SUM(Case WHEN status='IN' THEN (TIME_TO_SEC(tistamp)) ELSE 0 END )AS Intime)
)

/3600, 2) AS daily_hours

FROM punches p, employees e
WHERE p.emp_id=e.id
AND DATE_FORMAT(tistamp, '%m/%d/%y')='05/25/16'
GROUP BY p.emp_id

Thanks for your Time!