最近在做一个考勤系统，考勤主要关注的是缺勤、迟到和早退，目前的打卡控制器可以记录用户名和打卡时间，用户可能一天打卡多次，也可能一天只打了一次卡，这些情况都需要考虑。打卡信息都存储在考勤表中，从中要挖掘出一个月内的缺勤人员，迟到人员和早退人
最近在做一个考勤系统，考勤主要关注的是缺勤、迟到和早退，目前的打卡控制器可以记录用户名和打卡时间，用户可能一天打卡多次，也可能一天只打了一次卡，这些情况都需要考虑。打卡信息都存储在考勤表中，从中要挖掘出一个月内的缺勤人员，迟到人员和早退人员，并且能显示缺勤、迟到和早退的时间。
考勤表
create table [dbo].[kaoqin]( [user_name] [varchar](50) null, [card_time] [datetime] null) on [primary]go
插入测试数据insert into [master].[dbo].[kaoqin]select '张三', '2014-08-03 09:36:00'union allselect '张三', '2014-08-03 18:10:00'union allselect '张三', '2014-08-04 08:32:00'union allselect '张三', '2014-08-04 15:15:00'union allselect '张三', '2014-08-05 09:32:00'union allselect '张三', '2014-08-05 15:15:00'union allselect '张三', '2014-08-01 08:36:00'union allselect '张三', '2014-08-01 18:10:00'union allselect '张三', '2014-08-02 08:32:00'union allselect '张三', '2014-08-02 18:15:00'union allselect '张三', '2014-08-25 08:00:00'union allselect '张三', '2014-08-24 19:00:00'union allselect '张三', '2014-08-27 08:00:00'union allselect '张三', '2014-08-27 17:00:00'union allselect '张三', '2014-08-26 10:00:00'union allselect '张三', '2014-08-26 18:30:00'union allselect '张三', '2014-08-26 8:00:00'union allselect '张三', '2014-08-27 18:56:00' go
我的思路是用一张临时表得到这个月的所有工作日，将该临时表与用户进行交叉连接，这样每个用户在这个月的每个工作日都有一条记录。假设早上9点为上班时间，18点为下班时间，这个可以后续做成变量的形式。declare @time_start datetimedeclare @time_end datetime set @time_start = '2014-08-01 00:00:00'set @time_end = dateadd(m,1,@time_start)-- 一个月的工作日if object_id('tempdb..#tempdate') is not nullbegin drop table #tempdateendcreate table #tempdate( stat_day varchar(10))if object_id('tempdb..#tempuserdate') is not nullbegin drop table #tempuserdateendcreate table #tempuserdate( stat_day varchar(10), [user_name] varchar(40))create clustered index tempuserdate_index1 on #tempuserdate ([user_name],stat_day)declare @time_temp datetimeset @time_temp = @time_startwhile @time_temp 1 and datepart(weekday,@time_temp)'09:00:00'--早退select * from #tempuserdate aleft join( select [user_name],convert(varchar(10),card_time,121) as stat_day, min(card_time) as on_time,max(card_time) as off_time from [kaoqin] group by [user_name],convert(varchar(10),card_time,121)) b on a.[user_name]=b.[user_name] and a.stat_day=b.stat_daywhere convert(varchar(100), [b].[off_time], 8)<'18:00:00'
得到的结果
如果某个人他今天既迟到又早退在最终的结果中都会体现，可以从2014-08-05这条数据看出。当然，这个考勤系统还不完善，例如没有将节日考虑进来，初步的考虑是采用job定期存储每年的节日，如果员工请假，也需要纳入到系统的考虑中。