实现考勤数据转换SQL存储过程设计.doc

实现考勤数据转换SQL存储过程设计 　　摘 要：在考勤系统中，考勤机会记录下某人所有刷卡时间，而面对一条或多条考勤记录，我们还需要一个对比判断的过程来确定哪些记录是有效的上下班考勤记录。本文根据我校考勤规则设计了一个SQL存储过程，能够排除无效考勤记录，及时生成准确有效的上下班考勤记录，提供给用户网上查询。 关键词：考勤；SQL；存储过程 学校要求教员干部上下班必须刷卡考勤，规定晚于8：00刷卡为迟到，早于17：40刷卡为早退。目前，我校建有多个考勤机，24小时供教员干部刷卡考勤。但考勤机中只记录了某人刷卡时间，至于哪些记录是有效的，该记录是上班还是下班，没有实现智能判断。为便于校园网上及时查询，需要设计一段程序来智能判别有效上下班记录，并自动把一人多条考勤记录合成一人一条记录。 1 考勤记录的判别分析 当天考勤记录的各种情况可以通过下表来显示： 当某用户只有1条考勤记录时，考勤时间最大值T_max=最小值T_min，上班考勤时间Tcome和下班考勤时间Tgo的值会有2种情况：①“考勤记录时间=下班时间”，则Tcome为空，Tgo=T_max。 当某用户有2条以上考勤记录时，考勤时间存在最大值T_max和最小值T_min，上班考勤时间Tcome和下班考勤时间Tgo的值会有3种情况：①“T_max上班时间且T_min=下班时间且T_min=上班时间”，Tcome为空，Tgo=T_max。 通过上述分析可以看出，用户当天不管是只有一条考勤记录还是有多条记录，要判别出哪条记录是上班还是下班，确实比较复杂。如果简单地用最大值和最小值分别代替上班考勤时间和下班考勤时间，是不能真实反映出考勤状态的。另外，在数据量比较大的情况下，用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’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 　　/*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 no