Hellow experts ! I have a timesheets table as below
timesheets table
+-------------------------------------------+ |employee_no |time |device_id| +-------------------------------------------+ |1 |2019-09-17 07:00 |001 | |1 |2019-09-17 14:00 |002 | |2 |2019-09-19 08:00 |002 | |2 |2019-09-20 15:00 |003 | +-------------------------------------------+
I am using the following query to get the in and out times of the employees
select timesheets.employee_no,
MIN(time) as in_time,
MAX(time) as out_time,
COUNT(`time`) as record_count
from timesheets
where `time` between '2019-09-17 00:00:00'
and '2019-09-17 23:59:59'
group by timesheets.employee_no
and I get the desired output as expected as below
+----------------------------------------------------------------+ |employee_no |in_time |out_time |record_count| +----------------------------------------------------------------+ |1 |2019-09-17 07:00 |2019-09-17 14:00 |2 | |2 |2019-09-19 08:00 |2019-09-20 15:00 |2 | +---------------------------------------------------+------------+
Now i need to get the device_id’s of the the in and out records as time_in_device_id and time_out_device_id. How to achieve that ?
Advertisement
Answer
Do not use between with times. The following is simpler and more accurate.
MySQL does not offer “first()” and “last()” aggregation functions. However, you can use string manipulation to do what you want:
select ts.employee_no,
MIN(time) as in_time,
MAX(time) as out_time,
COUNT(time) as record_count,
SUBSTRING_INDEX(GROUP_CONCAT(device_id ORDER BY time), ',', 1) as first_device_id,
SUBSTRING_INDEX(GROUP_CONCAT(device_id ORDER BY time DESC), ',', 1) as lasst_device_id
from timesheets ts
where `time` >= '2019-09-17' and
`time` < '2019-09-18'
group by ts.employee_no