每天计算加班费

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时，您需要为它们提供实际的默认值。在极端情况下，如果NULL是由雇员一天来一天回家造成的，那么这些默认值可能分别是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演示

Answer 2

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