Re: Query w/sum and GROUP BY for related tables?
> 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"
Your `punches` table structure (id, emp_id, status, tistamp) puts punch-in and punch-out times for a given work episode in different rows, so queries reporting in and out times must match them up. SQL is designed for sets, not for row-to-row matching. It can be done, obviously, but it is cumbersome and inefficient, and doing it for each and every work episode to be reported will be slow if there are many of them.
So it's much more efficient to match them up at input time. When recording a punch clock time, the input module notes whether it's an in or out time. If in, it inserts a row; if out, it finds the matching row and updates that row's out time. MySQL doesn't have Instead Of Triggers, so it'd be simple input SQL.