CODEWITHSUNDEEP
mysqlsql
Michael So
Michael So

Reputation: 75

Calculate overtime per day

I have an sql code that gets the total work hours of each employee and his time in time out. I want to compute the total overtime of his work for the day. Can you help me with this? 8 hours is the regular hour per day.

Here is the codes

SELECT
  empno,
  date_created,
  time_in,
  time_out,
  time_format(timediff(time_out, time_in), '%H:%i') AS total_time
FROM
(
  SELECT empno, date_created,
    min(CASE WHEN status = 0 THEN time_created END) time_in,
    max(CASE WHEN status = 1 THEN time_created END) time_out
  FROM biometrics
  WHERE empno = 3
  GROUP BY empno, date_created
) t1;

SAMPLE OUTPUT

empno| date_created | time_in | time_out
  2      2013-07-15   11:08:07  15:00:00
  3      2013-07-15   11:50:00  NULL
  4      2013-07-15    NULL     16:00:00

What i want is something like this

empno | date_created | time_in | time_out | overtime
 2       2013-07-15    5:00:00  15:00:00      2

Upvotes: 2

Views: 1418

Answers (2)

peterm
peterm

Reputation: 92805

You can do something like this

SELECT empno, date_created, time_in, time_out, 
       CASE WHEN total_hours - 8 > 0 THEN total_hours - 8 ELSE 0 END overtime
  FROM
(
  SELECT empno, date_created, time_in, time_out,
         TIME_TO_SEC(TIMEDIFF(COALESCE(time_out, '17:00:00'),
                              COALESCE(time_in,  '09:00:00'))) / 3600 total_hours
    FROM
  (
    SELECT empno, date_created, 
           MIN(CASE WHEN status = 0 THEN time_created END) time_in,
           MIN(CASE WHEN status = 1 THEN time_created END) time_out
      FROM biometrics
     GROUP BY empno, date_created
  ) a
) b

Here is SQLFiddle demo

You need to provide real default values for time_in and time_out for cases when they are NULL. In an extreme case if NULLs are caused by the fact that employees come one day and go home the other day those default values might be 00:00:00 and 23:59:59 respectively since you are calculating overtime per calendar day.

UPDATE: if you want overtime to be presented in time format

SELECT empno, date_created, time_in, time_out, 
       SEC_TO_TIME(
         CASE WHEN total_sec - 28800 > 0 
              THEN total_sec - 28800 
              ELSE 0 END) overtime
  FROM
(
  SELECT empno, date_created, time_in, time_out,
         TIME_TO_SEC(TIMEDIFF(COALESCE(time_out, '17:00:00'),
                              COALESCE(time_in,  '09:00:00'))) total_sec
    FROM
  (
    SELECT empno, date_created, 
           MIN(CASE WHEN status = 0 THEN time_created END) time_in,
           MIN(CASE WHEN status = 1 THEN time_created END) time_out
      FROM biometrics
     GROUP BY empno, date_created
  ) a
) b

Here is SQLFiddle demo

Upvotes: 2

Arun Killu
Arun Killu

Reputation: 14253

SELECT IFNULL(TIMEDIFF('08:00:00',(TIMEDIFF(time_out,time_in))),0) 
AS OVERTIME
FROM biometrics

Upvotes: 0

Related Questions