考勤月度统计mysql_考勤查询统计SQL脚本

3.统计某一时间段内的员工的上下班打卡次数，以及迟到或早退30分钟以内的和30分钟以上的数据。

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_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