公司考勤只需一条Sql语句

select E.name,CONVERT(varchar(10),WK.nowDate,111) as nowDay,

min(dakaTime) as qiandaoTime,

MAX(dakaTime) as qiantuiTime,

case when min(dakaTime) > WK.workinTime and MIN(dakaTime)<DATEADD(HH,2,wk.workinTime) and MIN(dakatime)!=MAX(dakatime) Then '迟到'when MIN(dakaTime)>DATEADD(hh,2,wk.workintime) or MIN(dakatime)=MAX(dakatime) or min(dakaTime) is null then '正常'else '正常' end as 迟到,

case when max(dakaTime)<wk.workouttime and MIN(dakatime)!=MAX(dakatime) and dateadd(hh,2,MAX(dakatime))>wk.workouttime then '早退'when dateadd(hh,2,MAX(dakatime))<wk.workouttime or MIN(dakatime)=MAX(dakatime) or min(dakaTime) is null then '正常'else '正常' end as 早退,

case when MIN(dakaTime)>DATEADD(hh,2,wk.workintime)or dateadd(hh,2,MAX(dakatime))<wk.workouttime or MIN(dakatime)=MAX(dakatime) or (min(dakaTime) is null and wk.workDayState='工作日') then '缺勤'else '正常' end as 缺勤,

case when MIN(dakatime) IS not null and wk.workDayState='休息日' then '加班'else '正常' end as 加班

from WorkTime WK LEFT JOIN Employee E 

ON 1=1 

LEFT JOIN Daka

ON Daka.ygId=E.ygId 

and CONVERT(varchar(10),dakaTime,111)=WK.nowDate 

where WK.nowDate='2015-12-6'

group by CONVERT(varchar(10),WK.nowDate,111),E.ygId,E.name,WK.workinTime,WK.workoutTime,wk.workDayState