Kevin Reynolds Kevin Reynolds - 5 months ago 20
MySQL Question

SQL query to derive the the timeclock data from the measures table

First of all, big thank you for looking into this.

We have a table called measures which records that staff activity in the lab as shown below:


╔════╦═════════╦═════════════════════╦═════════════════════╦═════════╦══════════════╗
║ id ║ staf_id ║ start_time ║ stop_time ║ task_id ║ fruit_tested ║
╠════╬═════════╬═════════════════════╬═════════════════════╬═════════╬══════════════╣
║ 1 ║ 123 ║ 2016-06-01 10:00:00 ║ 2016-06-01 13:15:00 ║ 1 ║ 90 ║
║ 2 ║ 123 ║ 2016-06-01 15:20:00 ║ 2016-06-01 18:30:00 ║ 2 ║ 60 ║
║ 3 ║ 333 ║ 2016-06-02 10:30:30 ║ 2016-06-02 15:45:00 ║ 1 ║ 30 ║
║ 4 ║ 333 ║ 2016-06-03 09:00:00 ║ 2016-06-03 09:45:00 ║ 1 ║ 60 ║
║ 5 ║ 555 ║ 2016-06-01 07:00:00 ║ 2016-06-01 11:15:00 ║ 1 ║ 90 ║
║ 6 ║ 555 ║ 2016-06-01 11:30:00 ║ 2016-06-01 13:00:00 ║ 1 ║ 30 ║
║ 7 ║ 123 ║ 2016-06-02 10:00:00 ║ 2016-06-02 14:00:00 ║ 3 ║ 30 ║
║ 8 ║ 333 ║ 2016-06-03 10:20:00 ║ 2016-06-03 13:15:15 ║ 1 ║ 30 ║
║ 9 ║ 333 ║ 2016-06-04 07:50:20 ║ 2016-06-04 12:30:50 ║ 1 ║ 60 ║
║ 10 ║ 555 ║ 2016-06-03 05:30:00 ║ 2016-06-03 10:00:00 ║ 1 ║ 60 ║
║ 11 ║ 123 ║ 2016-06-03 06:00:00 ║ 2016-06-03 10:30:30 ║ 4 ║ 90 ║
║ 12 ║ 123 ║ 2016-06-03 12:15:04 ║ 2016-06-03 12:45:50 ║ 5 ║ 90 ║
║ 13 ║ 123 ║ 2016-06-03 17:10:00 ║ 2016-06-03 19:00:00 ║ 1 ║ 30 ║
║ 14 ║ 123 ║ 2016-06-04 12:15:04 ║ 2016-06-04 15:00:00 ║ 1 ║ 30 ║
║ 15 ║ 123 ║ 2016-06-04 15:10:00 ║ 2016-06-04 15:30:00 ║ 2 ║ 90 ║
╚════╩═════════╩═════════════════════╩═════════════════════╩═════════╩══════════════╝


Currently, we have an issue with our real timeClock table, as an interim solution I would like to retrieve time clock details of each staff for each day from the measures table, where the clock_in will be the first measures.start_time for the day and clock_out will be the the last measures.stop_time for the day of that staff as shown below:


╔══════════╦═════════════════════╦═════════════════════╗
║ staff_id ║ clock_in ║ clock_out ║
╠══════════╬═════════════════════╬═════════════════════╣
║ 123 ║ 2016-06-01 10:00:00 ║ 2016-06-01 18:30:00 ║
║ 123 ║ 2016-06-02 10:00:00 ║ 2016-06-02 14:00:00 ║
║ 123 ║ 2016-06-03 06:00:00 ║ 2016-06-03 19:00:00 ║
║ 123 ║ 2016-06-04 12:15:04 ║ 2016-06-04 15:30:00 ║
║ 333 ║ 2016-06-02 10:30:30 ║ 2016-06-02 15:45:00 ║
║ 333 ║ 2016-06-03 09:00:00 ║ 2016-06-03 13:15:15 ║
║ 333 ║ 2016-06-04 07:50:20 ║ 2016-06-04 12:30:50 ║
║ 555 ║ 2016-06-01 07:00:00 ║ 2016-06-01 13:00:00 ║
║ 555 ║ 2016-06-03 05:30:00 ║ 2016-06-03 10:00:00 ║
╚══════════╩═════════════════════╩═════════════════════╝


How do I achieve this result? Any suggestion would be deeply appreciated.

Answer

I think this is easy, just an aggregation:

select staff_id, min(start_time) as clock_in, max(stop_time) as clock_out
from measures
group by staff_id, date(start_time)
order by staff_id, min(start_time);
Comments