34,590
社区成员
发帖
与我相关
我的任务
分享
--我只写了两个,不一定对,反正就是把你需要的条件放case里面,存在就是1,不存在就是0,也可能是多个判断.
create table tb(name varchar(10),sdate varchar(10),jr_time varchar(10),wc_time varchar(10),mh_leave int,mh_overtime int,fixtime int,jr_fix int,wc_fix int,qq_fix int)
insert into tb values('aaa','2007-10-10','08:15:20','17:16:13',0,0,0,0,0,0)--(正常)
insert into tb values('aaa','2007-10-11','09:10:20','18:16:18',0,0,0,0,0,0)--(迟到)
insert into tb values('aaa','2007-10-12','08:10:20','17:09:17',0,0,0,0,0,0)--(早退)
insert into tb values('aaa','2007-10-13', null , null ,0,0,0,0,0,0)--(缺勤)
insert into tb values('aaa','2007-10-14','07:58:09', null ,0,0,0,0,0,0)--(无出)
insert into tb values('aaa','2007-10-15', null ,'17:09:02',0,0,0,0,0,0)--(无进)
insert into tb values('aaa','2007-10-10','09:15:20','17:16:13',1,0,0,0,0,0)--(正常)
insert into tb values('aaa','2007-10-11','10:10:20','18:16:18',1,0,0,0,0,0)--(迟到)
insert into tb values('aaa','2007-10-12','08:10:20','16:09:17',1,0,0,0,0,0)--(早退)
go
select name,sdate,jr_time,wc_time,mh_leave,fixtime,
cd_user = case when mh_leave <=0 and jr_time <= '09:00:00' then 0 else 1 end,
zt_user = case when jr_time is not null and wc_time is not null and mh_leave <=0 and datediff(hh , cast(sdate + ' ' + jr_time as datetime), cast(sdate + ' ' + wc_time as datetime)) >= 9 then 0 else 1 end
from tb
drop table tb
/*
name sdate jr_time wc_time mh_leave fixtime cd_user zt_user
---------- ---------- ---------- ---------- ----------- ----------- ----------- -----------
aaa 2007-10-10 08:15:20 17:16:13 0 0 0 0
aaa 2007-10-11 09:10:20 18:16:18 0 0 1 0
aaa 2007-10-12 08:10:20 17:09:17 0 0 0 0
aaa 2007-10-13 NULL NULL 0 0 1 1
aaa 2007-10-14 07:58:09 NULL 0 0 0 1
aaa 2007-10-15 NULL 17:09:02 0 0 1 1
aaa 2007-10-10 09:15:20 17:16:13 1 0 1 1
aaa 2007-10-11 10:10:20 18:16:18 1 0 1 1
aaa 2007-10-12 08:10:20 16:09:17 1 0 1 1
(所影响的行数为 9 行)
*/