Re: Query w/sum and GROUP BY for related tables?
If users clock in and out multiple times on the same date, you need to make a choice that depends on the requirement. If working time is usually summed by day, it may make more sense to have two tables, a table for employee workdays ...
workdays( wid, emp_id, date )
... and a table for their punchin and punchouts ...
punches( pid, wid, time_in, time_out)
... in which each row use wid to refer to a workday row for the day & employee, and specifies the in and out time. Then on the first punch-in of a given day for a given employee, the input module inserts one workday row and one punches row, and on subsequent events for that day and that employee, it either updates time_out in the open punches row for that day and that employee, or inserts a new punches row referring to the correct workday row and documenting the new punch-in.
The idea is to isolate all this row-to-row computation where it belongs, at the input stage.