考勤统计，一张表查询每月的员工考勤数据

近期根据公司人事要求，写了一些关于考勤统计的报表。主要是查询员工每月的考勤数据，要求一行数据显示员工的当月的每天的考勤信息，标的样式如下：

然后，根据考勤表，写了如下SQL语句，来实现报表的查询，谨以此做记录，以备后续查阅。

SELECT DeptName                  AS 所属部门,
       UserName                  AS 员工姓名,
       UserKQID                  AS 员工编号,
       Substring(WorkDate, 1, 7) AS 考勤月份,
       Count(( CASE
                 WHEN isworkday = 1
                      AND ( Datediff(minute, BeginTime, endtime) ) / 60 >= 9 THEN WorkDate
               END ))            AS 正常出勤天数,
       Count(( CASE
                 WHEN ( isworkday = 1
                        AND BeginTime = EndTime