近期根据公司人事要求，写了一些关于考勤统计的报表。主要是查询员工每月的考勤数据，要求一行数据显示员工的当月的每天的考勤信息，标的样式如下：
然后，根据考勤表，写了如下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
AND BeginTime > '12:00:00' )
OR ( isworkday = 1
AND BeginTime = EndTime
AND EndTime < '12:00:00' )
OR ( isworkday = 1
AND BeginTime IS NULL
AND EndTime IS NULL ) THEN WorkDate
END )) AS 异常出勤天数,
Count(( CASE
WHEN ( isworkday = 1
AND BeginTime = EndTime
AND BeginTime > '12:00:00' ) THEN WorkDate
END )) AS 上班未打卡,
Count(( CASE
WHEN ( isworkday = 1
AND BeginTime = EndTime
AND EndTime < '12:00:00' ) THEN WorkDate
END )) AS 下班未打卡,
Count(( CASE
WHEN ( isworkday = 1
AND BeginTime IS NULL
AND EndTime IS NULL ) THEN WorkDate
END )) AS 上下班未打卡,
Count(( CASE
WHEN isworkday = 1
AND BeginTime > '08:30:00'
AND BeginTime <= '08:40:00' THEN WorkDate
END )) AS 迟到小于十分钟,
Count(( CASE
WHEN isworkday = 1
AND BeginTime > '08:40:00' THEN WorkDate
END )) AS 迟到大于十分钟,
Count(( CASE
WHEN isworkday = 1
AND BeginTime IS NOT NULL
AND EndTime IS NOT NULL
AND BeginTime <> EndTime
AND 540 - Isnull(( Datediff(minute, BeginTime, endtime) ), 0) <= 10
AND 540 - Isnull(( Datediff(minute, BeginTime, endtime) ), 0) > 0 THEN WorkDate
END )) AS 早退小于十分钟,
Count(( CASE
WHEN isworkday = 1
AND BeginTime IS NOT NULL
AND EndTime IS NOT NULL
AND BeginTime <> EndTime
AND 540 - Isnull(( Datediff(minute, BeginTime, endtime) ), 0) > 10 THEN WorkDate
END )) AS 早退大于十分钟,
Min(( CASE
WHEN Substring(WorkDate, 9, 2) = '01' THEN BeginTime
END )) AS 上班1,
Max(( CASE
WHEN Substring(WorkDate, 9, 2) = '01' THEN endtime
END )) AS 下班1,
Sum(( CASE
WHEN Substring(WorkDate, 9, 2) = '01' THEN ( Datediff(minute, BeginTime, endtime) ) / 60
END )) AS 工作时长1,
Min(( CASE
WHEN Substring(WorkDate, 9, 2) = '02' THEN BeginTime
END )) AS 上班2,
Max(( CASE
WHEN Substring(WorkDate, 9, 2) = '02' THEN endtime
END )) AS 下班2,
Sum(( CASE
WHEN Substring(WorkDate, 9, 2) = '02' THEN ( Datediff(minute, BeginTime, endtime) ) / 60
END )) AS 工作时长2,
Min(( CASE
WHEN Substring(WorkDate, 9, 2) = '03' THEN BeginTime
END )) AS 上班3,
Max(( CASE
WHEN Substring(WorkDate, 9, 2) = '03' THEN endtime
END )) AS 下班3,
Sum(( CASE
WHEN Substring(WorkDate, 9, 2) = '03' THEN ( Datediff(minute, BeginTime, endtime) ) / 60
END )) AS 工作时长3,
Min(( CASE
WHEN Substring(WorkDate, 9, 2) = '04' THEN BeginTime
END )) AS 上班4,
Max(( CASE
WHEN Substring(WorkDate, 9, 2) = '04' THEN endtime
END )) AS 下班4,
Sum(( CASE
WHEN Substring(WorkDate, 9, 2) = '04' THEN ( Datediff(minute, BeginTime, endtime) ) / 60
END )) AS 工作时长4,
Min(( CASE
WHEN Substring(WorkDate, 9, 2) = '05' THEN BeginTime
END )) AS 上班5,
Max(( CASE
WHEN Substring(WorkDate, 9, 2) = '05' THEN endtime
END )) AS 下班5,
Sum(( CASE
WHEN Substring(WorkDate, 9, 2) = '05' THEN ( Datediff(minute, BeginTime, endtime) ) / 60
END )) AS 工作时长5,
Min(( CASE
WHEN Substring(WorkDate, 9, 2) = '06' THEN BeginTime
END )) AS 上班6,
Max(( CASE
WHEN Substring(WorkDate, 9, 2) = '06' THEN endtime
END )) AS 下班6,
Sum(( CASE
WHEN Substring(WorkDate, 9, 2) = '06' THEN ( Datediff(minute, BeginTime, endtime) ) / 60
END )) AS 工作时长6,
Min(( CASE
WHEN Substring(WorkDate, 9, 2) = '07' THEN BeginTime
END )) AS 上班7,
Max(( CASE
WHEN Substring(WorkDate, 9, 2) = '07' THEN endtime
END )) AS 下班7,
Sum(( CASE
WHEN Substring(WorkDate, 9, 2) = '07' THEN ( Datediff(minute, BeginTime, endtime) ) / 60
END )) AS 工作时长7,
Min(( CASE
WHEN Substring(WorkDate, 9, 2) = '08' THEN BeginTime
END )) AS 上班8,
Max(( CASE
WHEN Substring(WorkDate, 9, 2) = '08' THEN endtime
END )) AS 下班8,
Sum(( CASE
WHEN Substring(WorkDate, 9, 2) = '08' THEN ( Datediff(minute, BeginTime, endtime) ) / 60
END )) AS 工作时长8,
Min(( CASE
WHEN Substring(WorkDate, 9, 2) = '09' THEN BeginTime
END )) AS 上班9,
Max(( CASE
WHEN Substring(WorkDate, 9, 2) = '09' THEN endtime
END )) AS 下班9,
Sum(( CASE
WHEN Substring(WorkDate, 9, 2) = '09' THEN ( Datediff(minute, BeginTime, endtime) ) / 60
END )) AS 工作时长9,
Min(( CASE
WHEN Substring(WorkDate, 9, 2) = '10' THEN BeginTime
END )) AS 上班10,
Max(( CASE
WHEN Substring(WorkDate, 9, 2) = '10' THEN endtime
END )) AS 下班10,
Sum(( CASE
WHEN Substring(WorkDate, 9, 2) = '10' THEN ( Datediff(minute, BeginTime, endtime) ) / 60
END )) AS 工作时长10,
Min(( CASE
WHEN Substring(WorkDate, 9, 2) = '11' THEN BeginTime
END )) AS 上班11,
Max(( CASE
WHEN Substring(WorkDate, 9, 2) = '11' THEN endtime
END )) AS 下班11,
Sum(( CASE
WHEN Substring(WorkDate, 9, 2) = '11' THEN ( Datediff(minute, BeginTime, endtime) ) / 60
END )) AS 工作时长11,
Min(( CASE
WHEN Substring(WorkDate, 9, 2) = '12' THEN BeginTime
END )) AS 上班12,
Max(( CASE
WHEN Substring(WorkDate, 9, 2) = '12' THEN endtime
END )) AS 下班12,
Sum(( CASE
WHEN Substring(WorkDate, 9, 2) = '12' THEN ( Datediff(minute, BeginTime, endtime) ) / 60
END )) AS 工作时长12,
Min(( CASE
WHEN Substring(WorkDate, 9, 2) = '13' THEN BeginTime
END )) AS 上班13,
Max(( CASE
WHEN Substring(WorkDate, 9, 2) = '13' THEN endtime
END )) AS 下班13,
Sum(( CASE
WHEN Substring(WorkDate, 9, 2) = '13' THEN ( Datediff(minute, BeginTime, endtime) ) / 60
END )) AS 工作时长13,
Min(( CASE
WHEN Substring(WorkDate, 9, 2) = '14' THEN BeginTime
END )) AS 上班14,
Max(( CASE
WHEN Substring(WorkDate, 9, 2) = '14' THEN endtime
END )) AS 下班14,
Sum(( CASE
WHEN Substring(WorkDate, 9, 2) = '14' THEN ( Datediff(minute, BeginTime, endtime) ) / 60
END )) AS 工作时长14,
Min(( CASE
WHEN Substring(WorkDate, 9, 2) = '15' THEN BeginTime
END )) AS 上班15,
Max(( CASE
WHEN Substring(WorkDate, 9, 2) = '15' THEN endtime
END )) AS 下班15,
Sum(( CASE
WHEN Substring(WorkDate, 9, 2) = '15' THEN ( Datediff(minute, BeginTime, endtime) ) / 60
END )) AS 工作时长15,
Min(( CASE
WHEN Substring(WorkDate, 9, 2) = '16' THEN BeginTime
END )) AS 上班16,
Max(( CASE
WHEN Substring(WorkDate, 9, 2) = '16' THEN endtime
END )) AS 下班16,
Sum(( CASE
WHEN Substring(WorkDate, 9, 2) = '16' THEN ( Datediff(minute, BeginTime, endtime) ) / 60
END )) AS 工作时长16,
Min(( CASE
WHEN Substring(WorkDate, 9, 2) = '17' THEN BeginTime
END )) AS 上班17,
Max(( CASE
WHEN Substring(WorkDate, 9, 2) = '17' THEN endtime
END )) AS 下班17,
Sum(( CASE
WHEN Substring(WorkDate, 9, 2) = '17' THEN ( Datediff(minute, BeginTime, endtime) ) / 60
END )) AS 工作时长17,
Min(( CASE
WHEN Substring(WorkDate, 9, 2) = '18' THEN BeginTime
END )) AS 上班18,
Max(( CASE
WHEN Substring(WorkDate, 9, 2) = '18' THEN endtime
END )) AS 下班18,
Sum(( CASE
WHEN Substring(WorkDate, 9, 2) = '18' THEN ( Datediff(minute, BeginTime, endtime) ) / 60
END )) AS 工作时长18,
Min(( CASE
WHEN Substring(WorkDate, 9, 2) = '19' THEN BeginTime
END )) AS 上班19,
Max(( CASE
WHEN Substring(WorkDate, 9, 2) = '19' THEN endtime
END )) AS 下班19,
Sum(( CASE
WHEN Substring(WorkDate, 9, 2) = '19' THEN ( Datediff(minute, BeginTime, endtime) ) / 60
END )) AS 工作时长19,
Min(( CASE
WHEN Substring(WorkDate, 9, 2) = '20' THEN BeginTime
END )) AS 上班20,
Max(( CASE
WHEN Substring(WorkDate, 9, 2) = '20' THEN endtime
END )) AS 下班20,
Sum(( CASE
WHEN Substring(WorkDate, 9, 2) = '20' THEN ( Datediff(minute, BeginTime, endtime) ) / 60
END )) AS 工作时长20,
Min(( CASE
WHEN Substring(WorkDate, 9, 2) = '21' THEN BeginTime
END )) AS 上班21,
Max(( CASE
WHEN Substring(WorkDate, 9, 2) = '21' THEN endtime
END )) AS 下班21,
Sum(( CASE
WHEN Substring(WorkDate, 9, 2) = '21' THEN ( Datediff(minute, BeginTime, endtime) ) / 60
END )) AS 工作时长21,
Min(( CASE
WHEN Substring(WorkDate, 9, 2) = '22' THEN BeginTime
END )) AS 上班22,
Max(( CASE
WHEN Substring(WorkDate, 9, 2) = '22' THEN endtime
END )) AS 下班22,
Sum(( CASE
WHEN Substring(WorkDate, 9, 2) = '22' THEN ( Datediff(minute, BeginTime, endtime) ) / 60
END )) AS 工作时长22,
Min(( CASE
WHEN Substring(WorkDate, 9, 2) = '23' THEN BeginTime
END )) AS 上班23,
Max(( CASE
WHEN Substring(WorkDate, 9, 2) = '23' THEN endtime
END )) AS 下班23,
Sum(( CASE
WHEN Substring(WorkDate, 9, 2) = '23' THEN ( Datediff(minute, BeginTime, endtime) ) / 60
END )) AS 工作时长23,
Min(( CASE
WHEN Substring(WorkDate, 9, 2) = '24' THEN BeginTime
END )) AS 上班24,
Max(( CASE
WHEN Substring(WorkDate, 9, 2) = '24' THEN endtime
END )) AS 下班24,
Sum(( CASE
WHEN Substring(WorkDate, 9, 2) = '24' THEN ( Datediff(minute, BeginTime, endtime) ) / 60
END )) AS 工作时长24,
Min(( CASE
WHEN Substring(WorkDate, 9, 2) = '25' THEN BeginTime
END )) AS 上班25,
Max(( CASE
WHEN Substring(WorkDate, 9, 2) = '25' THEN endtime
END )) AS 下班25,
Sum(( CASE
WHEN Substring(WorkDate, 9, 2) = '25' THEN ( Datediff(minute, BeginTime, endtime) ) / 60
END )) AS 工作时长25,
Min(( CASE
WHEN Substring(WorkDate, 9, 2) = '26' THEN BeginTime
END )) AS 上班26,
Max(( CASE
WHEN Substring(WorkDate, 9, 2) = '26' THEN endtime
END )) AS 下班26,
Sum(( CASE
WHEN Substring(WorkDate, 9, 2) = '26' THEN ( Datediff(minute, BeginTime, endtime) ) / 60
END )) AS 工作时长26,
Min(( CASE
WHEN Substring(WorkDate, 9, 2) = '27' THEN BeginTime
END )) AS 上班27,
Max(( CASE
WHEN Substring(WorkDate, 9, 2) = '27' THEN endtime
END )) AS 下班27,
Sum(( CASE
WHEN Substring(WorkDate, 9, 2) = '27' THEN ( Datediff(minute, BeginTime, endtime) ) / 60
END )) AS 工作时长27,
Min(( CASE
WHEN Substring(WorkDate, 9, 2) = '28' THEN BeginTime
END )) AS 上班28,
Max(( CASE
WHEN Substring(WorkDate, 9, 2) = '28' THEN endtime
END )) AS 下班28,
Sum(( CASE
WHEN Substring(WorkDate, 9, 2) = '28' THEN ( Datediff(minute, BeginTime, endtime) ) / 60
END )) AS 工作时长28,
Min(( CASE
WHEN Substring(WorkDate, 9, 2) = '29' THEN BeginTime
END )) AS 上班29,
Max(( CASE
WHEN Substring(WorkDate, 9, 2) = '29' THEN endtime
END )) AS 下班29,
Sum(( CASE
WHEN Substring(WorkDate, 9, 2) = '29' THEN ( Datediff(minute, BeginTime, endtime) ) / 60
END )) AS 工作时长29,
Min(( CASE
WHEN Substring(WorkDate, 9, 2) = '30' THEN BeginTime
END )) AS 上班30,
Max(( CASE
WHEN Substring(WorkDate, 9, 2) = '30' THEN endtime
END )) AS 下班30,
Sum(( CASE
WHEN Substring(WorkDate, 9, 2) = '30' THEN ( Datediff(minute, BeginTime, endtime) ) / 60
END )) AS 工作时长30,
Min(( CASE
WHEN Substring(WorkDate, 9, 2) = '31' THEN BeginTime
END )) AS 上班31,
Max(( CASE
WHEN Substring(WorkDate, 9, 2) = '31' THEN endtime
END )) AS 下班31,
Sum(( CASE
WHEN Substring(WorkDate, 9, 2) = '31' THEN ( Datediff(minute, BeginTime, endtime) ) / 60
END )) AS 工作时长31
FROM dbo.UserSignList
GROUP BY DeptName,
UserName,
UserKQID,
Substring(WorkDate, 1, 7)
由于数据量可能会很大，公司的考勤数据后续做了分表处理，以及中间表处理。初步思路是根据中间表获取数据，新建一张中间表，把员工的考勤数据稍作处理，按照员工姓名、年份、月份分组，然后查询每和员工每月的考勤数据时，中间表的数据中，每个员工，每月数据仅有一条，查询的数量级非常小，速度也很快。至于中间表的设计，后续会有新的文章发出来，供大家参考，交流。
先看看查询的效果
转载自原文链接, 如需删除请联系管理员。
原文链接：考勤统计，一张表查询每月的员工考勤数据，转载请注明来源！