摘 要：在考勤系统中，考勤机会记录下某人所有刷卡时间，而面对一条或多条考勤记录，我们还需要一个对比判断的过程来确定哪些记录是有效的上下班考勤记录。本文根据我校考勤规则设计了一个sql存储过程，能够排除无效考勤记录，及时生成准确有效的上下班考勤记录，提供给用户网上查询。
　　关键词：考勤；sql；存储过程
　　学校要求教员干部上下班必须刷卡考勤，规定晚于8：00刷卡为迟到，早于17：40刷卡为早退。目前，我校建有多个考勤机，24小时供教员干部刷卡考勤。但考勤机中只记录了某人刷卡时间，至于哪些记录是有效的，该记录是上班还是下班，没有实现智能判断。为便于校园网上及时查询，需要设计一段程序来智能判别有效上下班记录，并自动把一人多条考勤记录合成一人一条记录。
　　1 考勤记录的判别分析
　　当天考勤记录的各种情况可以通过下表来显示：
　　当某用户只有1条考勤记录时，考勤时间最大值t_max=最小值t_min，上班考勤时间tcome和下班考勤时间tgo的值会有2种情况：①“考勤记录时间<下班时间”，则tcome=t_min，tgo为空；②“考勤记录时间>=下班时间”，则tcome为空，tgo=t_max。
　　当某用户有2条以上考勤记录时，考勤时间存在最大值t_max和最小值t_min，上班考勤时间tcome和下班考勤时间tgo的值会有3种情况：①“t_max<=上班时间且t_min<=上班时间”，tcome=t_min，tgo为空；②“t_max>上班时间且t_min<下班时间”，tcome=t_min，tgo=t_max；③“t_max>=下班时间且t_min>=上班时间”，tcome为空，tgo=t_max。wWW.11665.cOM
　　通过上述分析可以看出，用户当天不管是只有一条考勤记录还是有多条记录，要判别出哪条记录是上班还是下班，确实比较复杂。如果简单地用最大值和最小值分别代替上班考勤时间和下班考勤时间，是不能真实反映出考勤状态的。另外，在数据量比较大的情况下，用min和max函数进行统计，系统需要占用大量资源，查询效率比较低。所以，有必要设计一个考勤数据转换程序，把一人多条考勤记录转换成包含上下班时间的一人一条记录，提高查询效率。
　　2 程序设计
　　2.1 考勤表数据结构
　　创建sql考勤表jqr_inout，从各考勤机获取的考勤数据都汇总到此表中，字段有：
　　2.2 设计思路
　　通过程序处理，把有效最小值和最大值的数据标识出来，进而识别出有效的上下班考勤时间。表中字段done，表示处理状态，默认值为0。其中，0表示未处理的新建记录，2为最小值即上班时间，3为最大值即下班时间。
　　首先在下班前的时间中取出有效的最小值，即上班时间。
　　update jqr_inout set done=2 where done=0 and hm<'1740' and checktime=（select min（ja.checktime） from jqr_inout ja where ja.ssn=jqr_inout.ssn and ja.ymd= jqr_inout.ymd）
　　接着从上班后的时间中取出有效的最大值，即下班时间。
　　update jqr_inout set done=3 where done=0 and hm>'0800' and checktime=（select max（ja.checktime） from jqr_inout ja where ja.ssn=jqr_inout.ssn and ja.ymd=jqr_inout.ymd）
　　这样处理后，标识为2的是上班时间，标识为3的是下班时间。
　　2.3 程序改进
　　上述代码虽然能够标识出上下班时间，但是是一次性的，运行过程中还有一些情况需要进行处置。
　　一是考勤数据是实时采集的，数据会不断地增加进来，需要把已处理过的记录和待处理的记录以及处理中的记录区分开来。我们可以有字段done来表示，1-3表示正在处理的记录（1为未确定状态，2为最小值，3为最大值），4-9表示已经处理过的记录（4为最小值，5为最大值，9为无效值）。
　　二是随着考勤数据的增加，最小值和最大值也会发生变化，原来统计的最小值或最大值有可能不准确，需要对已处理过的最小值和最大值进行有效性判别。
　　三是程序在执行中有可能会意外中断，需要重新统计。
　　改进后的程序代码如下：
　　第1步，将所有新增记录标识为待处理状态，即把done由0变为1。为防止程序没有执行完意外中断，保证数据统计的正确性，此步可以同时将done由2或3变为1。此步执行后，done的值可能有1、4、5、9。
　　/*step1*/update jqr_inout set done = 1 where done in （0，2，3）
　　第2步，在done的值

1的数据中，取下班前的最小值标识为2，即把done由1变为2。此步执行后，done的值可能有1、2、4、5、9。
　　/*step2*/update jqr_inout set done=2 where done=1 and hm<'1740' and checktime=（select min（ja.checktime） from jqr_inout ja where ja.done not in （0，9） and ja.ssn=jqr_inout.ssn and ja.ymd=jqr_inout.ymd）
　　第3步，在done的值为1的数据中，取上班后的最大值标识为3，即把done由1变为3。此步执行后，done的值可能有1、2、3、4、5、9。 　/*step3 */update jqr_inout set done=3 where done=1 and hm>'0800' and checktime=（select max（ja.checktime） from jqr_inout ja where ja.done not in （0，9） and ja.ssn=jqr_inout.ssn and ja.ymd=jqr_inout.ymd）
　　第4步，把done的值为1的数据标识为无效，即把done由1变为9。
　　/*step4*/update jqr_inout set done=9 where done =1
　　此步执行后，done的值可能有2、3、4、5、9。某天某用户的考勤数据组合情况如下表。其中有7种情况需要标识出无效值，在第5步和第6步中分2步完成。
　　第5步，将234、235、2345中的4或5标识为9，将35、345中的5设为9，将245中的4设为9。
　　/*step5*/update jqr_inout set done=9
　　where done in （4，5） and （select count（*） from jqr_inout ja where ja.done in （2，3）
　　and ja.ssn=jqr_inout.ssn and ja.ymd=jqr_inout.ymd）=2
　　or done=5 and （select count（*） from jqr_inout jc where jc.done in （3，5）
　　and jc.ssn=jqr_inout.ssn and jc.ymd=jqr_inout.ymd） =2
　　or done=4 and （select count（*） from jqr_inout jb where jb.done in （2，4，5）
　　and jb.ssn=jqr_inout.ssn and jb.ymd=jqr_inout.ymd） =3
　　第6步，将24中的4，当时间在上班后时标识为3，否则为9。
　　/*step6*/update jqr_inout set done =（case when hm>’0800’ then 3 else 9 end）
　　where done=4 and （select count（*） from jqr_inout ja where ja.done in （2，4）
　　and ja.ssn=jqr_inout.ssn and ja.ymd=jqr_inout.ymd） = 2
　　第7步，上述2步执行后，done的值为2、3、4、5的都是有效值了。此时的2或4是最小值可作为上班时间，3或5是最大值可作为下班时间。取有变化的考勤记录，把一人多条考勤记录可以转换成了一人一条记录。可根据需要将相关数据导入其他特定表中供用户查询，这里只列出其中的select语句。
　　/*step7*/select distinct j.ymd as 年月日，j.ssn as 卡号，j.name as 姓名，
　　（select ja.checktime from jqr_inout ja where ja.ssn=j.ssn and ja.ymd=j.ymd
　　and ja.done in （2，4）） as tcome as 上班刷卡时间，
　　（select jb.checktime from jqr_inout jb where jb.ssn=j.ssn and jb.ymd=j.ymd
　　and jb.done in （3，5）） as tgo as 下班刷卡时间
　　from jqr_inout j where exists （select 1 from jqr_inout jc where jc.ssn=j.ssn
　　and jc.ymd=j.ymd and jc.done in （2，3）） order by j.ssn，m.wid
　　第8步，将2改为4，3改为5。完成数据转换处理过程。
　　/*step8*/update jqr_inout set done=（case when done=2 then 4 else 5 end）
　　where done in （2，3）
　　3 结束语
　　本文设计的sql存储过程，通过在考勤记录表中增加一个表示处理状态的字段done，然后经过一系列的数据对比处理，最终把有效的最小值和最大值标识出来，进而识别出有效的上下班考勤时间。在我校考勤系统中，我们把该存储过程放在相关程序中定期运行，实现了教员干部在网上及时准确地查询考勤信息的功能，取得了比较好的效果。
　　[参考文献]
　　[1]严苏丹.基于校园局域网的学生智能考勤系统研究与开发.科技信息，2012，6.
　　[2]邵东轶.基于web的考勤管理系统的设计与开发.计算机光盘软件与应用，2012，15.
　　[3]周勣.基于网络的考勤系统开发及应用.中国卫生资源，2012，1.
　　[4]万瑶，姚娟，赵文强.自动考勤系统的设计与实现，山西电子技术，2011，5.