求一条高难度SQL 语句,请各位帮忙想想办法.(50分)

  • 主题发起人 主题发起人 abcls
  • 开始时间 开始时间
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 &nbsp; &nbsp; &nbsp;Employeeid &nbsp; &nbsp; CardNo &nbsp; &nbsp; &nbsp;KqTime<br>1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;20 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 002501 &nbsp; &nbsp; &nbsp;2008-01-02 07:55:01<br>1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;20 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 002501 &nbsp; &nbsp; &nbsp;2008-01-02 07:55:08<br>1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;50 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 002515 &nbsp; &nbsp; &nbsp;2008-01-02 07:56:02<br>1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;20 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 002501 &nbsp; &nbsp; &nbsp;2008-01-02 12:08:06<br>1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;20 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 002501 &nbsp; &nbsp; &nbsp;2008-01-02 12:08:09<br>1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;20 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 002501 &nbsp; &nbsp; &nbsp;2008-01-02 12:08:11<br>1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;50 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 002515 &nbsp; &nbsp; &nbsp;2008-01-02 12:02:04<br>1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;20 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 002501 &nbsp; &nbsp; &nbsp;2008-01-02 12:50:37<br>1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;50 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 002515 &nbsp; &nbsp; &nbsp;2008-01-02 12:58:39<br>1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;50 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 002515 &nbsp; &nbsp; &nbsp;2008-01-02 12:59:40<br>1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;20 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 002501 &nbsp; &nbsp; &nbsp;2008-01-02 17:10:18<br>1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;20 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 002501 &nbsp; &nbsp; &nbsp;2008-01-02 17:10:19<br>1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;20 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 002501 &nbsp; &nbsp; &nbsp;2008-01-02 17:10:20<br>1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;50 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 002515 &nbsp; &nbsp; &nbsp;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 &nbsp;CurDate &nbsp;OnTime1 &nbsp; &nbsp;OffTime1 &nbsp; &nbsp; OnTime1 &nbsp; &nbsp; &nbsp; OffTime2<br>1 &nbsp; &nbsp; &nbsp; &nbsp;20 &nbsp; &nbsp; &nbsp;2008-01-02 &nbsp;07:55:01 &nbsp; &nbsp;12:08:11 &nbsp; &nbsp;12:50:37 &nbsp; &nbsp; 17:10:20<br>2 &nbsp; &nbsp; &nbsp; &nbsp;50 &nbsp; &nbsp; &nbsp;2008-01-02 &nbsp;07:56:02 &nbsp; &nbsp;12:02:04 &nbsp; &nbsp;12:58:39 &nbsp; &nbsp; &nbsp;17:01:03
 
又是一个做考勤系统的同行啊!<br>有空加我QQ聊聊!<br>93689974
 
建议:使用数据结构,而不是SQL来做。<br>原因:<br>一、调试方便<br>二、修改方便<br>三、可维护,可移植性强。
 
to yayongm<br>不太明白你所说的方法,能否说具体点呢?或者来个例子?
 
不知道这个是不是你需要的,感觉有点繁,有好的方法,请告诉我一声,学习一下,谢谢!<br><br>select a.employeeid,a.curdate,b.ontime1,c.offtime1,d.ontime2,e.offtime2 <br>from<br>(select distinct employeeid,substring(convert (varchar(20), kqtime,120),1,11) CurDate from kqRecord) &nbsp;as a<br><br>left join<br><br>(select employeeid, substring(convert (varchar(20), kqtime,120),1,11) CurDate,<br>min(substring(convert (varchar(20), kqtime,120),12,8)) OnTime1<br>from KqRecord where substring(convert (varchar(20), kqtime,120),12,8) between '07:30:01'and '08:00:00' <br>group by employeeid,substring(convert (varchar(20), kqtime,120),1,11)) as b on a.employeeid=b.employeeid and a.curdate=b.curdate<br><br>left join <br><br>(select employeeid,substring(convert (varchar(20), kqtime,120),1,11) as CurDate,<br>max(substring(convert (varchar(20), kqtime,120),12,8)) OffTime1<br>from KqRecord where substring(convert (varchar(20), kqtime,120),12,8) between '12:00:01'and '12:30:00' <br>group by employeeid,substring(convert (varchar(20), kqtime,120),1,11)) as c on a.employeeid=c.employeeid and a.curdate=c.curdate<br><br>left join<br><br>(select employeeid,substring(convert (varchar(20), kqtime,120),1,11) as CurDate,<br>min(substring(convert (varchar(20), kqtime,120),12,8)) onTime2<br>from KqRecord where substring(convert (varchar(20), kqtime,120),12,8) between '12:30:01'and '13:00:00' <br>group by employeeid,substring(convert (varchar(20), kqtime,120),1,11)) as d on a.employeeid=d.employeeid and a.curdate=d.curdate<br><br>left join<br><br>(select employeeid,substring(convert (varchar(20), kqtime,120),1,11) as CurDate,<br>max(substring(convert (varchar(20), kqtime,120),12,8)) OffTime2<br>from KqRecord where substring(convert (varchar(20), kqtime,120),12,8) between '17:00:01'and '18:30:00' <br>group by employeeid,substring(convert (varchar(20), kqtime,120),1,11)) as e on a.employeeid=e.employeeid and e.curdate=a.curdate<br>但是我觉得还是有点疑问,比如我12:01下班但是我12:20就上班了,这个怎么统计,你不能让员工必须到12:30以后才能打卡吧
 
insert into dayReport <br>&nbsp; select employeeid,convert(varchar(10),kqtime,23) as curdate,<br>&nbsp; &nbsp; &nbsp; min(case when convert(varchar(8),Kqtime,8) between '07:30' and '08:30' then convert(varchar(8),Kqtime,8) else null end ) onTime1,<br>&nbsp; &nbsp; &nbsp; max(case when convert(varchar(8),Kqtime,8) between '12:00' and '12:30' then convert(varchar(8),Kqtime,8) else null end ) offTime1,<br>&nbsp; &nbsp; &nbsp; min(case when convert(varchar(8),Kqtime,8) between '12:30' and '13:00' then convert(varchar(8),Kqtime,8) else null end ) onTime2,<br>&nbsp; &nbsp; &nbsp; max(case when convert(varchar(8),Kqtime,8) between '17:00' and '18:30' then convert(varchar(8),Kqtime,8) else null end ) offTime2<br>&nbsp; from KqRecord &nbsp;where convert(varchar(10),kqtime,23)='2008-01-02'<br>&nbsp; group by employeeid,convert(varchar(10),kqtime,23)<br>WHERE 条件值是暂时用的,通用的条件值由你根据你的程序来确定,OK了吧
 
学习楼上
 
谢谢laiaseven, 你的方法不错!
 
后退
顶部