--员工考核表
declare @a table(
empno varchar(20),
work_date datetime,
is_card varchar(3)
)
insert into @a
select 'x1','2013-05-01',''
union
select 'x1','2013-05-02',''--打卡记录表
declare @b table(
empno varchar(20),
val_date datetime,
val_time char(5)
)insert into @b
select 'x1','2013-05-01','07:00'
union
select 'x1','2013-05-01','18:01'
union
select 'x1','2013-05-02','08:15'
union
select 'x1','2013-05-02','18:01'select * from @a
select * from @b
--期望得到以下结果,通过打卡记录,判断一个员工是否打满了卡
--(迟到,或忘记打卡,都不计算),只计算1 YES/NO 即可.
--empno work_date is_card
--x1 2013-05-01 YES --
--x1 2013-05-02 NO
---------------
员工考勤时间是
上午 07:00-->08:00
下午 18:00-->19:00
--我是楼主,补充1下.
--期望得到以下结果,通过打卡记录,判断一个员工是否打满了卡
--(迟到,或忘记打卡,都不计算),只计算1 YES/NO 即可.
--empno work_date is_card
--x1 2013-05-01 YES
--x1 2013-05-02
--为提高效率,只要一个YES 即可,NO 可以不要.--员工考勤时间是
--上午 07:00-->08:00
--下午 18:00-->19:00
;with cte as(
select empno,val_date,max(case when val_time<='12:00' then val_time end)st,max(case when val_time>'12:00' then val_time end)et
from @b group by empno,val_date
)update @a set is_card=isnull(b.is_card,'NO') from @a a left join (
select *,(case when st between '07:00' and '08:00' and et between '18:00' and '19:00' then 'YES' else 'NO' end)is_card from cte
)b on a.empno=b.empno and a.work_date=b.val_date/*
empno work_date is_card
x1 2013-05-01 00:00:00.000 YES
x1 2013-05-02 00:00:00.000 NO
*/
你好,我要的是UPDATE语句,本人搞前台的,后台SQL不怎么会,能麻烦帮忙改1下么? 非常感谢
--查询语句
select a.empno,
a.work_date,
b1.val_time 'am',
b2.val_time 'pm'
,
case
when b1.val_time between '07:00' and '08:00' and b2.val_time between '18:00' and '19:00'
then 'Yes'
else 'No'
end 'Is_Card'
from
@a a inner join
@b b1
on a.empno=b1.empno and a.work_date=b1.val_date
inner join @b b2
on b1.val_date=b2.val_date and b1.empno=b2.empno
and b1.val_time<>b2.val_time
and b1.val_time<'18:00'--修改仅是Yes
update a set
a.is_card='Yes'
from
@a a inner join
@b b1
on a.empno=b1.empno and a.work_date=b1.val_date
inner join @b b2
on b1.val_date=b2.val_date and b1.empno=b2.empno
and b1.val_time<>b2.val_time
and b1.val_time<'18:00'
and b1.val_time between '07:00' and '08:00' and b2.val_time between '18:00' and '19:00'
set a.is_card='YES'
from @a a,( select empno,val_date
from @b
group by empno,val_date
having min(val_time)<= '08:00'
and max(val_time) >= '18:00') b
where a.empno=b.empno and a.work_date=b.val_date