Milan Soni Milan Soni - 7 days ago 7
MySQL Question

Get total hours worked in a day, total in hours, total out hours mysql

I have a MySQL table where employee in and out timings are recorded.

id device_id punch stat_log
1 26 2016-11-23 11:30:05 in
2 26 2016-11-23 14:30:05 out
3 26 2016-11-23 15:00:05 in
4 26 2016-11-23 20:32:05 out


Is it possible to retrieve total in hours and total out hours from total hours worked by a user using single query?

I used below query:

SELECT
device_id as d_id,
date(run_start) as start_date,
sum(elapsed_min)/60.0 as hrs_on,
24 - sum(elapsed_min)/60.0 as hrs_off
FROM
(SELECT *, TIMESTAMPDIFF(minute, run_start, run_end) elapsed_min
FROM
(SELECT t1.device_id, t1.punch as run_start,
(SELECT MIN(punch)
FROM emp_punch_in_out t2
LEFT JOIN emp_map e ON e.device_id = t2.device_id
WHERE t2.device_id = t1.device_id
AND t2.punch > t1.punch
AND t2.stat_log = 'out') as run_end
FROM emp_punch_in_out t1
WHERE t1.device_id = 26 AND t1.stat_log = 'in') t
) tt
GROUP BY device_id, start_date
ORDER BY device_id, start_date


From this query i got the result like:

#ID device_id Date In_hours Out_hours
1 26 2016-11-23 8.5333 15.4667


but i want result like total in hours and total out hours from working hours in a day including break hours in out hours.

Desired Output

#ID device_id Date In_hours Out_hours
1 26 2016-11-23 8.5333 0.30

Answer

Assuming that your input table is called Table1:

Select device_id,
    Date(punch) As "Date",
    Sum(Case When stat_log = 'out' Then TimeStampDiff(minute, punch, next_punch) Else 0 End)/60 As out_hours,
    Sum(Case When stat_log = 'in' Then TimeStampDiff(minute, punch, next_punch) Else 0 End)/60 As in_hours
  From (
Select id, device_id, punch, stat_log, 
    (Select punch 
       From Table1 As n
       Where n.device_id = c.device_id
         And n.punch > c.punch
       Order By punch asc
       Limit 1) As next_punch
  From Table1 As c) As x
  Group By device_id, Date(punch);
Comments