A
abcls
Unregistered / Unconfirmed
GUEST, unregistred user!
我在完成一考勤统计模块.<br>其中有一考勤原始记录表KqRecord,其字段如下:<br>Autoid int identity(1,1) Primary key,<br>Employeeid int,(对应员工表Employee的Autoid)<br>CardNo Varchar(20), <br>KqTime DateTime<br>其记录如下: <br>Autoid Employeeid CardNo KqTime<br>1 20 002501 2008-01-02 07:55:01<br>1 20 002501 2008-01-02 07:55:08<br>1 50 002515 2008-01-02 07:56:02<br>1 20 002501 2008-01-02 12:08:06<br>1 20 002501 2008-01-02 12:08:09<br>1 20 002501 2008-01-02 12:08:11<br>1 50 002515 2008-01-02 12:02:04<br>1 20 002501 2008-01-02 12:50:37<br>1 50 002515 2008-01-02 12:58:39<br>1 50 002515 2008-01-02 12:59:40<br>1 20 002501 2008-01-02 17:10:18<br>1 20 002501 2008-01-02 17:10:19<br>1 20 002501 2008-01-02 17:10:20<br>1 50 002515 2008-01-02 17:01:03<br><br>我现在要对上面记录进行统计.并把结果保存在员工考勤日报表DayReport表中<br>DayReport的表结构如下:<br>Autoid int identity(1,1) Primary key,<br>Employeeid int,(对应员工表Employee的Autoid)<br>curDate Varchar(10),<br>OnTime1 Varchar(8),<br>OffTime1 Varchar(8),<br>OnTime2 Varchar(8),<br>OffTime2 Varchar(8)<br>考勤统计规则:一天必须打四次卡,07:30:01~08:30:00之间打一次,12:00:01~12:30:00之间打一次,12:30:01~13:00:00之间打一次,17:00:01~18:30:00之间打一次,在这四个打卡时间范围以外的记录,打卡无效,对于上班卡,取时间最早的记录,对于下班卡,取时间最晚的记录.<br>统计后结果如下:<br>Autoid Employeeid CurDate OnTime1 OffTime1 OnTime1 OffTime2<br>1 20 2008-01-02 07:55:01 12:08:11 12:50:37 17:10:20<br>2 50 2008-01-02 07:56:02 12:02:04 12:58:39 17:01:03