老东家就考勤管的严.
上午8:30上班
中午11:30签退,午休
下午13:30签到,开始工作
晚上18点下班.
中午签退的时间范围是 11:30至12点
下午签到的时间范围是 13:00至13:30
HR怕11:30打完卡,下午3点再回来上班.央企都是各色人等。这也造成了人力管理的复杂.
实验结构和数据:
-
create table t (
-
id int primary key auto_increment,
-
uid varchar(10),
-
ts timestamp
-
);
-
-
insert into t(uid,ts) values(‘大柴‘,‘2015-12-16 08:20:39‘);
-
insert into t(uid,ts) values(‘二柴‘,‘2015-12-16 08:30:39‘);
-
insert into t(uid,ts) values(‘大柴‘,‘2015-12-16 11:30:39‘);
-
insert into t(uid,ts) values(‘大柴‘,‘2015-12-16 11:45:39‘);
-
insert into t(uid,ts) values(‘二柴‘,‘2015-12-16 11:35:00‘);
-
insert into t(uid,ts) values(‘大柴‘,‘2015-12-16 13:30:39‘);
-
insert into t(uid,ts) values(‘二柴‘,‘2015-12-16 13:20:00‘);
-
insert into t(uid,ts) values(‘大柴‘,‘2015-12-16 18:30:39‘);
-
insert into t(uid,ts) values(‘二柴‘,‘2015-12-16 17:59:00‘);
-
insert into t(uid,ts) values(‘三柴‘,‘2015-12-16 08:28:00‘);
-
insert into t(uid,ts) values(‘三柴‘,‘2015-12-16 17:59:00‘);
-
insert into t(uid,ts) values(‘小柴‘,‘2015-12-16 10:59:00‘);
-
insert into t(uid,ts) values(‘小柴‘,‘2015-12-16 13:59:00‘);
SQL统计:
-
select
-
ts 日期,
-
uid 姓名,
-
case
-
when t1.a is null then ‘异常‘
-
when extract(HOUR_MINUTE from a)<=0829 then a
-
when extract(HOUR_MINUTE from a) >0829 then concat(‘迟到:‘,a) end 上午上班打卡,
-
if(b is null,‘异常‘,b) 上午下班打卡,
-
if(c is null,‘异常‘,c) 下午上班打卡,
-
case
-
when t1.d is null then ‘异常‘
-
when extract(HOUR_MINUTE from d)>1759 then d
-
when extract(HOUR_MINUTE from d) <=1759 then concat(‘早退:‘,d) end 下午下班打卡
-
from (
-
select
-
date_format(ts,‘%Y-%m-%d‘) ts,uid,
-
min(
-
case when
-
extract(HOUR_MINUTE from ts)<1130
-
then ts else null end
-
) a,
-
min(
-
case when
-
extract(HOUR_MINUTE from ts)>=1130 AND
-
extract(HOUR_MINUTE from ts)<=1159
-
then ts else null end
-
) b,
-
min(
-
case when
-
extract(HOUR_MINUTE from ts)>=1300 AND
-
extract(HOUR_MINUTE from ts)<=1329
-
then ts else null end
-
) c,
-
max(
-
case when
-
extract(HOUR_MINUTE from ts)>1329
-
then ts else null end
-
) d
-
from t
-
group by
-
date_format(ts,‘%Y-%m-%d‘) ,uid
-
) t1 order by 上午上班打卡;
考勤统计问题
原文：http://blog.itpub.net/29254281/viewspace-1873635/
