计算每天的加班时间

Question

我有一个sql代码，可以获取每个员工的总工作时间及其超时时间。我想计算他当天的总加班时间。你能帮我吗？8小时是每天的常规时间。

这是代码

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;

样品输出

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

我想要的是这样的

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

Answer 1

你可以做这样的事情

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

这是 SQLFiddle 演示

您需要为提供真正的默认值time_in，并time_out针对当他们NULL。在一个极端的情况下，如果NULLs是由员工有一天到另一天回家而造成的，那么这些默认值可能分别是00:00:00和，23:59:59因为您要计算每个日历日的加班时间。

更新： 如果要overtime以时间格式显示

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

这是 SQLFiddle 演示