这句UPDATE语句该如何写?
--员工考核表
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 即可.
--empnowork_dateis_card
--x12013-05-01 YES --
--x12013-05-02 NO
;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
/*
empnowork_dateis_card
x12013-05-01 00:00:00.000YES
x12013-05-02 00:00:00.000NO
*/
