1、嚼但匙噻首先是查询某员工的考勤记录。可以根据年份，月份，或者时间段查询结果，同时也可以去掉人员筛选条件，查询多个人的考勤结果。以及计算出该员工的打卡是否正常。SQL语句如下所示：SELECT 员工号,卡号,姓名,日期, '打卡记录' AS 考勤类型, Substring(日期, 1, 4) AS 年份, Substring(日期, 6, 2) AS 月份, Datename(weekday, 日期) AS 工作日类型, Min(打卡时间) AS 上班打卡时间, Max(打卡时间) AS 下班打卡时间, CASE WHEN Datename(weekday, 日期) <> '星期六' AND Datename(weekday, 日期) <> '星期日' THEN( CASE WHEN Min(打卡时间) = Max(打卡时间) THEN'上班或下班忘打卡' WHEN Min(打卡时间) > '08:00:00' THEN '迟到' WHEN Max(打卡时间) < '17:00:00' THEN '早退' ELSE '正常' END ) ELSE '非工作日打卡' END AS 状态FROM (SELECT a.[emp_id] 员工号, a.[card_id] 卡号, b.[emp_fname] 姓名, CONVERT(CHAR(10), sign_time, 120) 日期, CONVERT(VARCHAR, sign_time, 108) 打卡时间, [sign_time] FROM [dbo].[TimeRecords] a LEFT JOIN [dbo].[Employee] b ON a.emp_id = b.emp_id AND a.[card_id] = b.[card_id] WHERE a.emp_id IS NOT NULL AND a.emp_id <> '' AND CONVERT(CHAR(10), sign_time, 120) BETWEEN '2018-09-01' AND '2018-10-30' AND b.[emp_fname] = '姓名') AS mmGROUP BY mm.员工号, 卡号, 姓名, mm.日期ORDER BY 员工号, 日期 ASC
2、根据部门，员工姓名、日期查询员工的打卡记录。查询的SQL语句如下所示：SELECT a.emp_fname, b.depart_name, CONVERT(CHAR(10), sign_time, 120) date, Min(c.sign_time) AS BeginTime, Max(c.sign_time) AS EndTime Datediff(minute, Min(c.sign_time), Max(c.sign_time)) AS minute,FROM Employee a LEFT JOIN Departs b ON a.depart_id = b.depart_id LEFT JOIN TimeRecords c ON a.emp_id = c.emp_idWHERE Substring(a.depart_id, 1, 3) = '041' AND CONVERT(CHAR(10), sign_time, 120) BETWEEN '2018-11-01' AND '2018-11-30' AND a.emp_id IS NOT NULLGROUP BY depart_name, emp_fname, CONVERT(CHAR(10), sign_time, 120)ORDER BY depart_name, emp_fname, CONVERT(CHAR(10), sign_time, 120) ASC查询结果如下所示：
3、统计厢咆廨炝某一时间段内的员工的上下班打卡次数，以及迟到或早退30分钟以内的和30分钟以上的数据。SELECT a.emp_fname, b.depart_nam髫潋啜缅e, CONVERT(CHAR(10), sign_time, 120) date, Min(c.sign_time) AS BeginTime, Max(c.sign_time) AS EndTime Datediff(minute, Min(c.sign_time), Max(c.sign_time)) AS minute,FROM Employee a LEFT JOIN Departs b ON a.depart_id = b.depart_id LEFT JOIN TimeRecords c ON a.emp_id = c.emp_idWHERE Substring(a.depart_id, 1, 3) = '041' AND CONVERT(CHAR(10), sign_time, 120) BETWEEN '2018-11-01' AND '2018-11-30' AND a.emp_id IS NOT NULLGROUP BY depart_name, emp_fname, CONVERT(CHAR(10), sign_time, 120)ORDER BY depart_name, emp_fname, CONVERT(CHAR(10), sign_time, 120) ASCSELECT b.emp_fname, b.depart_name, Sum(CASE WHEN ms = 0 AND CONVERT(CHAR(10), EndTime, 23) < '12:00:00' THEN 1 ELSE 0 END) AS 上班未打卡, Sum(CASE WHEN ms = 0 AND CONVERT(CHAR(10), EndTime, 23) > '12:00:00' THEN 1 ELSE 0 END) AS 下班未打卡, Sum(CASE WHEN 540 - ms BETWEEN 0 AND 30 THEN 1 ELSE 0 END) AS 迟到或早退30分钟以内, Sum(CASE WHEN 540 - ms > 30 THEN 1 ELSE 0 END) AS 迟到或早退30分钟以上FROM (SELECT TOP 100 PERCENT a.emp_fname, b.depart_name, Min(c.sign_time) AS BeginTime, Max(c.sign_time) AS EndTime, Datediff(minute, Min(c.sign_time), Max(c.sign_time)) AS ms FROM Employee a LEFT JOIN Departs b ON a.depart_id = b.depart_id LEFT JOIN TimeRecords c ON a.emp_id = c.emp_id WHERE Substring(a.depart_id, 1, 3) = '041' AND CONVERT(CHAR(10), sign_time, 120) BETWEEN '2018-11-01' AND '2018-11-30' AND a.emp_id IS NOT NULL GROUP BY depart_name, emp_fname, CONVERT(CHAR(10), sign_time, 120) ORDER BY depart_name, emp_fname, CONVERT(CHAR(10), sign_time, 120) ASC) AS bGROUP BY emp_fname, depart_nameORDER BY depart_name, emp_fname查询结果如下图所示
4、统计各种假期的请假时长 ：SELECT applyUser, ApplyDept, Sum(CASE WHEN leavetype = '事假' THEN leaveHours ELSE 0 END) AS 事假, Sum(CASE WHEN leavetype = '病假' THEN leaveHours ELSE 0 END) AS 病假, Sum(CASE WHEN leavetype = '产检假' THEN leaveHours ELSE 0 END) AS 产前病事假, Sum(CASE WHEN leavetype = '产假' OR LeaveType = '陪产假' THEN leaveHours ELSE 0 END) AS 产假或护理假, Sum(CASE WHEN leavetype = '婚假' THEN leaveHours ELSE 0 END) AS 婚假, Sum(CASE WHEN leavetype = '丧假' THEN leaveHours ELSE 0 END) AS 丧假, Sum(CASE WHEN leavetype = '年休假' THEN leaveHours ELSE 0 END) AS 年假, Sum(CASE WHEN leavetype = '公假' THEN leaveHours ELSE 0 END) AS 公假, Sum(CASE WHEN leavetype = '其他' THEN leaveHours ELSE 0 END) AS 其他, Sum(CASE WHEN leavetype = '调休' THEN leaveHours ELSE 0 END) AS 调休, Sum(CASE WHEN leavetype = '工伤假' THEN leaveHours ELSE 0 END) AS 工伤假FROM HR_LeaveWHERE CompanyId = 16 AND Node = 2 AND CONVERT(CHAR(10), Start, 120) BETWEEN '2018-09-01' AND '2018-12-30'GROUP BY ApplyDept, ApplyUserId, ApplyUserSQL执行结果如下所示：
5、根据加班类型，统计各种加班类型的加班时长：/****** Script for SelectTopNRows command from SSMS ******/SELECT [ApplyUser], [ApplyDept], Sum(CASE WHEN [OtType] = '平时延长' THEN Duration ELSE 0 END) AS 平时延长, Sum(CASE WHEN [OtType] = '休息日' THEN Duration ELSE 0 END) AS 休息日, Sum(CASE WHEN [OtType] = '法定节假日' THEN Duration ELSE 0 END) AS 法定节假日FROM HR_OvertimeWHERE CompanyId = 16 AND Node = 2 AND CONVERT(CHAR(10), CreateDateTime, 120) BETWEEN '2018-09-01' AND '2018-12-30'GROUP BY [ApplyDept], ApplyUserId, [ApplyUser]结果如下图所示：
