34,590
社区成员
发帖
与我相关
我的任务
分享
select distinct e.name,d.name,
sum(case when datediff(hour,a.inTime,a.outTime)>2 then 1
when 2<datediff(hour,a.inTime,a.outTime)<12 then 1.5
when datediff(hour,a.inTime,a.outTime)>12 then 2
else 0
end)as 下井次数
from attendance a ,employee e,department d
where a.cardNum=e.cardnumber and d.id=e.departmentId
and a.occDay between 20131213 and 20131222
--and datediff(hour,a.inTime,a.outTime)>2
group by e.name,d.name