根据上下班的打卡记录计算工时regular,update + case..when + if,进行多种打卡情况的计算。
UPDATE tmp2
SET tmp2.regular = CASE
# 只有一次打卡记录
WHEN clock_in = clock_out THEN '0'
# 迟到+早退
WHEN classes = '白班' AND CONCAT(time_one," ",clock_in) >= DATE_ADD(CONCAT(time_one," ",work_one), INTERVAL 5 MINUTE) AND clock_out < offwork_one
THEN TRUNCATE(TIMESTAMPDIFF(MINUTE,CONCAT(time_one," ",work_one),CONCAT(time_one," ",offwork_one))/60,1)
- IF(TRUNCATE(TIMESTAMPDIFF(MINUTE,CONCAT(time_one," ",work_one), CONCAT(time_one," ",clock_in))/60,1) - FLOOR(TIMESTAMPDIFF(MINUTE,CONCAT(time_one," ",work_one), CONCAT(time_one," ",clock_in))/60) >= '0.5' , CEILING(TIMESTAMPDIFF(MINUTE,CONCAT(time_one," ",work_one), CONCAT(time_one," ",clock_in))/60) , FLOOR(TIMESTAMPDIFF(MINUTE,CONCAT(time_one," ",work_one), CONCAT(time_one," ",clock_in))/60) + '0.5')
- IF(TRUNCATE(TIMESTAMPDIFF(MINUTE,CONCAT(time_one," ",clock_out),CONCAT(time_one," ",offwork_one))/60,1) - FLOOR(TIMESTAMPDIFF(MINUTE,CONCAT(time_one," ",clock_out),CONCAT(time_one," ",offwork_one))/60) >= '0.5' , CEILING(TIMESTAMPDIFF(MINUTE,CONCAT(time_one," ",clock_out),CONCAT(time_one," ",offwork_one))/60) , FLOOR(TIMESTAMPDIFF(MINUTE,CONCAT(time_one," ",clock_out),CONCAT(time_one," ",offwork_one))/60) + '0.5')
- rest
# 迟到+正常下班
WHEN classes = '白班' AND CONCAT(time_one," ",clock_in) >= DATE_ADD(CONCAT(time_one," ",work_one), INTERVAL 5 MINUTE) AND clock_out >= offwork_one
THEN TRUNCATE(TIMESTAMPDIFF(MINUTE,CONCAT(time_one," ",work_one),CONCAT(time_one," ",offwork_one))/60,1)
- IF(TRUNCATE(TIMESTAMPDIFF(MINUTE,CONCAT(time_one," ",work_one), CONCAT(time_one," ",clock_in))/60,1) - FLOOR(TIMESTAMPDIFF(MINUTE,CONCAT(time_one," ",work_one), CONCAT(time_one," ",clock_in))/60) >= '0.5' , CEILING(TIMESTAMPDIFF(MINUTE,CONCAT(time_one," ",work_one), CONCAT(time_one," ",clock_in))/60) , FLOOR(TIMESTAMPDIFF(MINUTE,CONCAT(time_one," ",work_one), CONCAT(time_one," ",clock_in))/60) + '0.5')
- rest
# 正常上班+早退
WHEN classes = '白班' AND CONCAT(time_one," ",clock_in) < DATE_ADD(CONCAT(time_one," ",work_one), INTERVAL 5 MINUTE) AND clock_out < offwork_one
THEN TRUNCATE(TIMESTAMPDIFF(MINUTE,CONCAT(time_one," ",work_one),CONCAT(time_one," ",offwork_one))/60,1)
- IF(TRUNCATE(TIMESTAMPDIFF(MINUTE,CONCAT(time_one," ",clock_out),CONCAT(time_one," ",offwork_one))/60,1) - FLOOR(TIMESTAMPDIFF(MINUTE,CONCAT(time_one," ",clock_out),CONCAT(time_one," ",offwork_one))/60) >= '0.5' , CEILING(TIMESTAMPDIFF(MINUTE,CONCAT(time_one," ",clock_out),CONCAT(time_one," ",offwork_one))/60) , FLOOR(TIMESTAMPDIFF(MINUTE,CONCAT(time_one," ",clock_out),CONCAT(time_one," ",offwork_one))/60) + '0.5')
- rest
# 正常上班+正常下班
WHEN classes = '白班' AND CONCAT(time_one," ",clock_in) < DATE_ADD(CONCAT(time_one," ",work_one), INTERVAL 5 MINUTE) AND clock_out >= offwork_one
THEN TRUNCATE(TIMESTAMPDIFF(MINUTE,CONCAT(time_one," ",work_one),CONCAT(time_one," ",offwork_one))/60,1)
- rest
# 迟到(第一天)+早退(第一天)
WHEN classes = '夜班' AND CONCAT(time_one," ",clock_in) >= DATE_ADD(CONCAT(time_one," ",work_one), INTERVAL 5 MINUTE) AND clock_out >= work_one
THEN TRUNCATE(TIMESTAMPDIFF(MINUTE,CONCAT(time_one," ",work_one),DATE_ADD(CONCAT(time_one," ",offwork_one), INTERVAL 1 DAY))/60,1)
- IF(TRUNCATE(TIMESTAMPDIFF(MINUTE,CONCAT(time_one," ",work_one), CONCAT(time_one," ",clock_in))/60,1) - FLOOR(TIMESTAMPDIFF(MINUTE,CONCAT(time_one," ",work_one), CONCAT(time_one," ",clock_in))/60) >= '0.5' , CEILING(TIMESTAMPDIFF(MINUTE,CONCAT(time_one," ",work_one), CONCAT(time_one," ",clock_in))/60) , FLOOR(TIMESTAMPDIFF(MINUTE,CONCAT(time_one," ",work_one), CONCAT(time_one," ",clock_in))/60) + '0.5')
- IF(TRUNCATE(TIMESTAMPDIFF(MINUTE,CONCAT(time_one," ",clock_out),DATE_ADD(CONCAT(time_one," ",offwork_one), INTERVAL 1 DAY))/60,1) - FLOOR(TIMESTAMPDIFF(MINUTE,CONCAT(time_one," ",clock_out),DATE_ADD(CONCAT(time_one," ",offwork_one), INTERVAL 1 DAY))/60) >= '0.5' , CEILING(TIMESTAMPDIFF(MINUTE,CONCAT(time_one," ",clock_out),DATE_ADD(CONCAT(time_one," ",offwork_one), INTERVAL 1 DAY))/60) , FLOOR(TIMESTAMPDIFF(MINUTE,CONCAT(time_one," ",clock_out),DATE_ADD(CONCAT(time_one," ",offwork_one), INTERVAL 1 DAY))/60) + '0.5')
- rest
# 迟到(第一天)+早退(第二天)
WHEN classes = '夜班' AND CONCAT(time_one," ",clock_in) >= DATE_ADD(CONCAT(time_one," ",work_one), INTERVAL 5 MINUTE) AND clock_out < offwork_one
THEN TRUNCATE(TIMESTAMPDIFF(MINUTE,CONCAT(time_one," ",work_one),DATE_ADD(CONCAT(time_one," ",offwork_one), INTERVAL 1 DAY))/60,1)
- IF(TRUNCATE(TIMESTAMPDIFF(MINUTE,CONCAT(time_one," ",work_one), CONCAT(time_one," ",clock_in))/60,1) - FLOOR(TIMESTAMPDIFF(MINUTE,CONCAT(time_one," ",work_one), CONCAT(time_one," ",clock_in))/60) >= '0.5' , CEILING(TIMESTAMPDIFF(MINUTE,CONCAT(time_one," ",work_one), CONCAT(time_one," ",clock_in))/60) , FLOOR(TIMESTAMPDIFF(MINUTE,CONCAT(time_one," ",work_one), CONCAT(time_one," ",clock_in))/60) + '0.5')
- IF(TRUNCATE(TIMESTAMPDIFF(MINUTE,DATE_ADD(CONCAT(time_one," ",clock_out), INTERVAL 1 DAY),DATE_ADD(CONCAT(time_one," ",offwork_one), INTERVAL 1 DAY))/60,1) - FLOOR(TIMESTAMPDIFF(MINUTE,DATE_ADD(CONCAT(time_one," ",clock_out), INTERVAL 1 DAY),DATE_ADD(CONCAT(time_one," ",offwork_one), INTERVAL 1 DAY))/60) >= '0.5' , CEILING(TIMESTAMPDIFF(MINUTE,DATE_ADD(CONCAT(time_one," ",clock_out), INTERVAL 1 DAY),DATE_ADD(CONCAT(time_one," ",offwork_one), INTERVAL 1 DAY))/60) , FLOOR(TIMESTAMPDIFF(MINUTE,DATE_ADD(CONCAT(time_one," ",clock_out), INTERVAL 1 DAY),DATE_ADD(CONCAT(time_one," ",offwork_one), INTERVAL 1 DAY))/60) + '0.5')
- rest
# 迟到(第二天)+早退(第二天)
WHEN classes = '夜班' AND clock_in < offwork_one AND clock_out < offwork_one
THEN TRUNCATE(TIMESTAMPDIFF(MINUTE,CONCAT(time_one," ",work_one),DATE_ADD(CONCAT(time_one," ",offwork_one), INTERVAL 1 DAY))/60,1)
- IF(TRUNCATE(TIMESTAMPDIFF(MINUTE,CONCAT(time_one," ",work_one), DATE_ADD(CONCAT(time_one," ",clock_in), INTERVAL 1 DAY))/60,1) - FLOOR(TIMESTAMPDIFF(MINUTE,CONCAT(time_one," ",work_one), DATE_ADD(CONCAT(time_one," ",clock_in), INTERVAL 1 DAY))/60) >= '0.5' , CEILING(TIMESTAMPDIFF(MINUTE,CONCAT(time_one," ",work_one), DATE_ADD(CONCAT(time_one," ",clock_in), INTERVAL 1 DAY))/60) , FLOOR(TIMESTAMPDIFF(MINUTE,CONCAT(time_one," ",work_one), DATE_ADD(CONCAT(time_one," ",clock_in), INTERVAL 1 DAY))/60) + '0.5')
- IF(TRUNCATE(TIMESTAMPDIFF(MINUTE,DATE_ADD(CONCAT(time_one," ",clock_out), INTERVAL 1 DAY),DATE_ADD(CONCAT(time_one," ",offwork_one), INTERVAL 1 DAY))/60,1) - FLOOR(TIMESTAMPDIFF(MINUTE,DATE_ADD(CONCAT(time_one," ",clock_out), INTERVAL 1 DAY),DATE_ADD(CONCAT(time_one," ",offwork_one), INTERVAL 1 DAY))/60) >= '0.5' , CEILING(TIMESTAMPDIFF(MINUTE,DATE_ADD(CONCAT(time_one," ",clock_out), INTERVAL 1 DAY),DATE_ADD(CONCAT(time_one," ",offwork_one), INTERVAL 1 DAY))/60) , FLOOR(TIMESTAMPDIFF(MINUTE,DATE_ADD(CONCAT(time_one," ",clock_out), INTERVAL 1 DAY),DATE_ADD(CONCAT(time_one," ",offwork_one), INTERVAL 1 DAY))/60) + '0.5')
- rest
# 正常上班+早退(第一天)
WHEN classes = '夜班' AND CONCAT(time_one," ",clock_in) < DATE_ADD(CONCAT(time_one," ",work_one), INTERVAL 5 MINUTE) AND clock_out >= work_one
THEN TRUNCATE(TIMESTAMPDIFF(MINUTE,CONCAT(time_one," ",work_one),DATE_ADD(CONCAT(time_one," ",offwork_one), INTERVAL 1 DAY))/60,1)
- IF(TRUNCATE(TIMESTAMPDIFF(MINUTE,CONCAT(time_one," ",clock_out),DATE_ADD(CONCAT(time_one," ",offwork_one), INTERVAL 1 DAY))/60,1) - FLOOR(TIMESTAMPDIFF(MINUTE,CONCAT(time_one," ",clock_out),DATE_ADD(CONCAT(time_one," ",offwork_one), INTERVAL 1 DAY))/60) >= '0.5' , CEILING(TIMESTAMPDIFF(MINUTE,CONCAT(time_one," ",clock_out),DATE_ADD(CONCAT(time_one," ",offwork_one), INTERVAL 1 DAY))/60) , FLOOR(TIMESTAMPDIFF(MINUTE,CONCAT(time_one," ",clock_out),DATE_ADD(CONCAT(time_one," ",offwork_one), INTERVAL 1 DAY))/60) + '0.5')
- rest
# 正常上班+早退(第二天)
WHEN classes = '夜班' AND CONCAT(time_one," ",clock_in) < DATE_ADD(CONCAT(time_one," ",work_one), INTERVAL 5 MINUTE) AND clock_out < offwork_one
THEN TRUNCATE(TIMESTAMPDIFF(MINUTE,CONCAT(time_one," ",work_one),DATE_ADD(CONCAT(time_one," ",offwork_one), INTERVAL 1 DAY))/60,1)
- IF(TRUNCATE(TIMESTAMPDIFF(MINUTE,DATE_ADD(CONCAT(time_one," ",clock_out), INTERVAL 1 DAY),DATE_ADD(CONCAT(time_one," ",offwork_one), INTERVAL 1 DAY))/60,1) - FLOOR(TIMESTAMPDIFF(MINUTE,DATE_ADD(CONCAT(time_one," ",clock_out), INTERVAL 1 DAY),DATE_ADD(CONCAT(time_one," ",offwork_one), INTERVAL 1 DAY))/60) >= '0.5' , CEILING(TIMESTAMPDIFF(MINUTE,DATE_ADD(CONCAT(time_one," ",clock_out), INTERVAL 1 DAY),DATE_ADD(CONCAT(time_one," ",offwork_one), INTERVAL 1 DAY))/60) , FLOOR(TIMESTAMPDIFF(MINUTE,DATE_ADD(CONCAT(time_one," ",clock_out), INTERVAL 1 DAY),DATE_ADD(CONCAT(time_one," ",offwork_one), INTERVAL 1 DAY))/60) + '0.5')
- rest
# 迟到(第一天)+正常下班
WHEN classes = '夜班' AND CONCAT(time_one," ",clock_in) >= DATE_ADD(CONCAT(time_one," ",work_one), INTERVAL 5 MINUTE) AND clock_out >= offwork_one
THEN TRUNCATE(TIMESTAMPDIFF(MINUTE,CONCAT(time_one," ",work_one),DATE_ADD(CONCAT(time_one," ",offwork_one), INTERVAL 1 DAY))/60,1)
- IF(TRUNCATE(TIMESTAMPDIFF(MINUTE,CONCAT(time_one," ",work_one), CONCAT(time_one," ",clock_in))/60,1) - FLOOR(TIMESTAMPDIFF(MINUTE,CONCAT(time_one," ",work_one), CONCAT(time_one," ",clock_in))/60) >= '0.5' , CEILING(TIMESTAMPDIFF(MINUTE,CONCAT(time_one," ",work_one), CONCAT(time_one," ",clock_in))/60) , FLOOR(TIMESTAMPDIFF(MINUTE,CONCAT(time_one," ",work_one), CONCAT(time_one," ",clock_in))/60) + '0.5')
- rest
# 迟到(第二天)+正常下班
WHEN classes = '夜班' AND clock_in < offwork_one AND clock_out >= offwork_one
THEN TRUNCATE(TIMESTAMPDIFF(MINUTE,CONCAT(time_one," ",work_one),DATE_ADD(CONCAT(time_one," ",offwork_one), INTERVAL 1 DAY))/60,1)
- IF(TRUNCATE(TIMESTAMPDIFF(MINUTE,CONCAT(time_one," ",work_one), DATE_ADD(CONCAT(time_one," ",clock_in), INTERVAL 1 DAY))/60,1) - FLOOR(TIMESTAMPDIFF(MINUTE,CONCAT(time_one," ",work_one), DATE_ADD(CONCAT(time_one," ",clock_in), INTERVAL 1 DAY))/60) >= '0.5' , CEILING(TIMESTAMPDIFF(MINUTE,CONCAT(time_one," ",work_one), DATE_ADD(CONCAT(time_one," ",clock_in), INTERVAL 1 DAY))/60) , FLOOR(TIMESTAMPDIFF(MINUTE,CONCAT(time_one," ",work_one), DATE_ADD(CONCAT(time_one," ",clock_in), INTERVAL 1 DAY))/60) + '0.5')
- rest
# 正常上班+正常下班
WHEN classes = '夜班' AND CONCAT(time_one," ",clock_in) < DATE_ADD(CONCAT(time_one," ",work_one), INTERVAL 5 MINUTE) AND clock_out >= offwork_one
THEN TRUNCATE(TIMESTAMPDIFF(MINUTE,CONCAT(time_one," ",work_one),DATE_ADD(CONCAT(time_one," ",offwork_one), INTERVAL 1 DAY))/60,1)
- rest
END;