2

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
Milan Soni
  • 108
  • 1
  • 9

1 Answers1

2

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
         And Date(n.punch) = Date(c.punch)
       Order By punch asc
       Limit 1) As next_punch
  From Table1 As c) As x
  Group By device_id, Date(punch);

Edit

Added And Date(n.punch) = Date(c.punch) to the join predicate so that this works for multiple days.

mendosi
  • 2,001
  • 1
  • 12
  • 18
  • Note I didn't add the `id` field because it seems sort of meaningless. – mendosi Nov 25 '16 at 06:13
  • '26', '2016-11-23', '0.5000', '8.5333' – kapilpatwa93 Nov 25 '16 at 06:13
  • perfect :-) Thanks @mendosi – Milan Soni Nov 25 '16 at 06:14
  • yeah @kapil.dev that i want as my desired result. – Milan Soni Nov 25 '16 at 06:14
  • @MilanSoni Note the edit! My test input data only covered one day so I didn't think to join on date as well, otherwise the 'out' from the end of one day will match up to the first 'in' the next day. – mendosi Nov 25 '16 at 07:04
  • yeah i want multiple days log so this is perfect but when i add two more rows of in and out stat_log then it doesn't count in and out hours :( – Milan Soni Nov 25 '16 at 07:37
  • 5 26 2016-11-25 13:02:41 in AND 6 26 2016-11-25 13:03:14 out then it will not count in hours 2.5 – Milan Soni Nov 25 '16 at 07:39
  • Yeah sorry my mistake. It is perfect. :P – Milan Soni Nov 25 '16 at 07:52
  • Hi @mendosi is there any way to calculate work hours by shift. For example if employee punch in 7 PM and punch out 4 AM of different date. Thanks in advance for your help. – Milan Soni Feb 09 '17 at 09:34
  • @MilanSoni hmm... it would be possible to modify the query to consider previous days matching to an 'out' but if someone can work a shift that goes over midnight then they could also have their meal break over midnight and then things become tricky. So, maybe you will need to find a different way of defining each worker's workday. – mendosi Feb 09 '17 at 09:54