sql 条件判断语句？在线等

llh6795 2010-09-10 05:25:33

select employeeid,convert(varchar(30),dateadd("hh",-3,datetimebrush),111) kqsj,min(datetimebrush) as zhongbs,max(datetimebrush) as zhongbx,'中班' as bcbs,'1' as sbts,
case when datediff(minute,min(datetimebrush),max(datetimebrush))<30 then 1 else 0 end as wscs,
case when datediff(minute,convert(nvarchar(15),'16:30:00.000',114),convert(nvarchar(15),min(datetimebrush),114))>2 and datename(hour,min(datetimebrush))<20 then 1 else 0 end as cdcs,
case when datediff(minute,convert(nvarchar(15),'22:00:00.000',114),convert(nvarchar(30),convert(nvarchar(30),dateadd(hour,-3,max(datetimebrush)),121),114))<-2 and datename(hour,dateadd(hour,-3,max(datetimebrush)) )>17 then 1 else 0 end as ztcs
from lsb201009 t
where convert(varchar(30),dateadd("hh",-3,datetimebrush),108) between '11:30:00' and '23:59:59' and worktypename='倒班' and not exists(select 1 from lsb201009 where employeeid=t.employeeid
and datetimebrush between dateadd(day,1,convert(nvarchar(25),(convert(nvarchar(10),datetimebrush,121)+' 03:01:00.000'),126)) and dateadd(day,1,convert(nvarchar(25),(convert(nvarchar(10),datetimebrush,121)+' 14:29:00.000'),126)) )
group by employeeid,convert(varchar(30),dateadd("hh",-3,datetimebrush),111)
having count(*)>=2

上面红色的那行麻烦大家帮我看看，为什么早退次数（ztcs）统计不出来结果？

...全文