一段根据考勤记录统计考勤数据的SQL

今天遇到的问题，记录一下。

--select
    --userName,
    --recordTime = month(getdate()),
    --onDutyLateCount = sum(case amOnDutyStatus when 'late' then 1 else 0 end),
    --offDutyEarlyCount = sum(case pmOffDutyStatus when 'early' then 1 else 0 end)
--from
    --checkDuty
--where
    --month(getdate()) = 10
--group by userName

select userID,userName,userDep,convert(char(7),checkTime,120) AS recordTime,
       onDutyLateCount=sum(case when amOnDutyStatus='late' then 1 else 0 end),
       offDutyEarlyCount=sum(case when pmOffDutyStatus='early' then 1 else 0 end)
 from checkDuty
 where year(checkTime) = 2005 and month(checkTime) = 10 and userDep = '增值事业部' and userName = 'admin'
 group by userID,userName,userDep,convert(char(7),checkTime,120)
order by userID desc