人事考勤系统：多个时间段多笔可能重复打卡数据处理问题，期盼高人出现



------
请各位达人帮忙看下此问题，小弟初来乍到，不懂规矩的地方请各位达人海涵，问题详细如下：  
公司有HR系统，需对其中的报表做一个改善，其中一段SQL语法不会：
有考勤刷卡记录表，表名为attendance ，有如下字段：
姓名 卡号 刷卡时间 刷卡类型 name id time type  
张三 59775623 2010-04-01 07:23:37 null  
张三 59775623 2010-04-01 07:50:21 null  
张三 59775623 2010-04-01 18:20:22 null  
张三 59775623 2010-04-01 18:50:53 null  
李四 59775624 2010-04-01 07:00:06 null  
李四 59775624 2010-04-01 18:00:12 null  
李四 59775624 2010-04-02 08:20:32 null
李四 59775624 2010-04-02 17:00:22 null
李四 59775624 2010-04-01 18:00:08 null
.....................................................................................................
以下还有很多，每位员工每天都有，...............
现在需要更新刷卡的数据，需要对表attendance执行一个update动作，根据刷卡时间，需满足如下功能

1.如果刷卡时间是8:00以前，则type的值update之后就为“上班”；

2.如果刷卡时间是17:30以后，则type的值update之后就为“下班”；

3.如果刷卡时间为8:00~~12:00之间，则type的值update之后就为“迟到”；

4.如果刷卡时间为13:00~~17:30之间，则type的值update之后就为“早退”；

5.如果同一个人同一天在12:00以前有多次刷卡，则刷卡时间最早的那一笔记录其type值为“上班”，其余12:00以前的刷卡记录其type值update之后，变为“上班重复刷卡；

6. 如果同一个人同一天在13:00以后有多次刷卡，则刷卡时间最迟的那一笔记录其type值为“下班”，其余13:00以后的刷卡记录其type值update之后，变为“下班重复刷卡；

7.其余每天的任何时间段，update后，type值变为“乱刷卡”

小弟最头痛的是其中的5、6两种情况，可以使用where + group by + haviing count(*)>1将其查出来,update就不知道如何处理了，小弟思考了好几天，也只能做到这一步，实在做不下去了，跑来求助各位达人；

问题补充：
1.请各位达人务必注意那个时间的格式，SQL里面转换时间格式可以使用convert(char(10),time,120)，输出为YYYYMMDD;convert(char(8),time,112),输出格式为YYYYMMDD;convert(char(10),time,108)，输出为HH-MM-SS

------
慢慢折腾。。。非三五句能说完
对5、6条，更应该取一次有效卡次，比如上班最最迟的，下班取最早的一次。。其他该忽略
------
刚起来，就看到happycell188的回复，太厉害了，马上进入实际环境测试，先谢谢happycell188和huckyrandom的回复
------
引用 2 楼 happycell188 的回复:
SQL code
--------------------SQL Server数据格式化工具-------------------
---------------------------------------------------------------
-- DESIGNER :happycell188(喜喜)
-- QQ :584738179
-- Developme……

------
人事考勤系统难就难在，怎样判断进出标志，还有让敲卡时间和请假时间去比对，得出结果
------
引用 5 楼 happycell188 的回复:
SQL code
'修改... SQL Server 2000环境测试通过！'
--------------------SQL Server数据格式化工具-------------------
---------------------------------------------------------------
-- DESIGNER :happycell188(喜喜)
-- ……

------
学习学习！
------
回复加分
------
up~~~~~~
------
引用 7 楼 htl258 的回复:
引用 5 楼 happycell188 的回复:
SQL code
'修改... SQL Server 2000环境测试通过！'
--------------------SQL Server数据格式化工具-------------------
---------------------------------------------------------------
-- DESIGN……

------
学习中，支持~
------
學習下
------
ding^^^^^^^^^^^^^^^^
------
你是哪家公司啊，做HR软件，，我们也做HR软件的。
------
学习了
------
SQL code--------------------SQL Server数据格式化工具-------------------
---------------------------------------------------------------
-- DESIGNER :happycell188(喜喜)
-- QQ :584738179
-- Development Tool :Microsoft Visual C++ 6.0 C Language 
-- FUNCTION :CONVERT DATA TO T-SQL
---------------------------------------------------------------
-- Microsoft SQL Server 2005
-- Developer Edition on Microsoft Windows XP [版本 5.1.2600]
---------------------------------------------------------------
---------------------------------------------------------------

use test
go
if object_id('test.dbo.attendance') is not null drop table attendance 
-- 创建数据表
create table attendance 
(
name char(5),
id int,
time datetime,
type char(20)
)
go
--插入测试数据
insert into attendance select '张三',59775623,'2010-04-01 07:23:37',null
union all select '张三',59775623,'2010-04-01 07:50:21',null
union all select '张三',59775623,'2010-04-01 18:20:22',null
union all select '张三',59775623,'2010-04-01 18:50:53',null
union all select '李四',59775624,'2010-04-01 07:00:06',null
union all select '李四',59775624,'2010-04-01 18:00:12',null
union all select '李四',59775624,'2010-04-02 08:20:32',null
union all select '李四',59775624,'2010-04-02 17:00:22',null
union all select '李四',59775624,'2010-04-02 18:18:08',null
union all select '王五',59775625,'2010-04-01 08:02:06',null
union all select '王五',59775625,'2010-04-01 18:00:12',null
union all select '王五',59775625,'2010-04-02 07:20:32',null
union all select '王五',59775625,'2010-04-02 12:35:22',null
union all select '王五',59775625,'2010-04-02 18:18:08',null
go
--代码实现

-->更新数据
update attendance set type=t2.type
from attendance t1
inner join
(
  select name,id,time=_time,type=case when time<='08:00' and idd=1 then '上班'
  when time>'08:00' and time<='12:00' and idd=1 then '迟到'
  when time<'12:00' and idd<>1 then '上班重复刷卡'
  when time>='13:00' and time<='17:30' and idd=1 then '早退'
  when time>'17:30' and idd=1 then '下班'
  when time>'13:00' and idd<>1 then '下班重复刷卡' 
  when time>='12:00' and time<='13:00' then '乱刷卡' end
  from
  (
  select name,id,_time=time,time=convert(varchar(5),time,8),type,
  idd=row_number()over(partition by convert(varchar(10),time,120),name order by time)
  from attendance where convert(varchar(5),time,8)<='12:00'
  union all
  select name,id,_time=time,time=convert(varchar(5),time,8),type,
  idd=row_number()over(partition by convert(varchar(10),time,120),name order by time)
  from attendance where convert(varchar(5),time,8)>='13:00'
  union all
  select name,id,_time=time,time=convert(varchar(5),time,8),type,idd=0
  from attendance where convert(varchar(5),time,8)>='12:00'
  and convert(varchar(5),time,8)<='13:00'
  )t
) t2
on t1.id=t2.id and t1.time=t2.time

-->显示更新后数据
select * from attendance

/*测试结果

name id time type
--------------------------------------------------------------
张三 59775623 2010-04-01 07:23:37.000 上班  
张三 59775623 2010-04-01 07:50:21.000 上班重复刷卡  
张三 59775623 2010-04-01 18:20:22.000 下班  
张三 59775623 2010-04-01 18:50:53.000 下班重复刷卡  
李四 59775624 2010-04-01 07:00:06.000 上班  
李四 59775624 2010-04-01 18:00:12.000 下班  
李四 59775624 2010-04-02 08:20:32.000 迟到  
李四 59775624 2010-04-02 17:00:22.000 早退  
李四 59775624 2010-04-02 18:18:08.000 下班重复刷卡  
王五 59775625 2010-04-01 08:02:06.000 迟到  
王五 59775625 2010-04-01 18:00:12.000 下班  
王五 59775625 2010-04-02 07:20:32.000 上班  
王五 59775625 2010-04-02 12:35:22.000 乱刷卡  
王五 59775625 2010-04-02 18:18:08.000 下班  

(14 行受影响)
*/

'这样应该可以实现的，你试试吧..
------
路过 路过 路过
------
mark

------
學習下
------
不错，谢谢！
------
毕业前实习时搞过一个绩效的系统
上面涉及到打卡的问题，当时只是简单的处理一下
上班取最早、下班取最晚
哈哈哈
------
好用吗？谢谢
------
学习中。。。
------

------

------
不错不错！
------
进来学习一下咯
------
SQL操作还这么强大，学无止境啊！向楼上的牛人敬礼！
------
果然强大，又学到东西了 谢谢大家
------
引用 5 楼 happycell188 的回复:
SQL code
'修改... SQL Server 2000环境测试通过！'
--------------------SQL Server数据格式化工具-------------------
---------------------------------------------------------------
-- DESIGNER :happycell188(喜喜)
-- ……

------
呵呵，刚学了点
看到那人写的，高手

------
有点复杂
------
又学到东西了
------
dingssssssss
------
学到了东西
------
楼主想多了。

我记得考勤的MM说过，不确定自己是否打卡的话可以多刷。
------
这个根据时间来判断就没问题了啊
------
你还没有考虑到没有打卡怎么处理,如果上午请假了.13点来的公司.18点离开你的程序会不会出错.
如果早上来了,下午走的时候没打卡. 或者加班到了第二天早上才打卡
------
路过看看
------
学习了

------
引用 2 楼 happycell188 的回复:
SQL code
--------------------SQL Server数据格式化工具-------------------
---------------------------------------------------------------
-- DESIGNER :happycell188(喜喜)
-- QQ :584738179
-- Developme……

------
学习学习！！http://www.szewayit.com
------
学习学习！！http://www.szewayit.com
------
还有什么可以帮忙的吗 
以后有什么关于SQL要做，我愿意尝试去做，反正我课少
------
标记下
------
处理上只是简单地处理了有效打卡的部分，整个考勤中考勤钟数据判断如果结合整个考勤记录，包括请假，加班和外出登记后，还是很复杂的。

ｍａｒｋ一个学习。
------
好帖，我也正在研究HR系统，不过是在买的系统上改
------
最近也在做这样一个系统，学习中
------
我们公司刚好在做同样的模块，crm里面的在线打卡系统，考虑的很多：请假，病假、孕妇晚来早走、早退、节假日、周末、不同时区的同事等，貌似很复杂，这几天还在技术方案探讨上面，
------
关注中~~~~~`
------
不过貌似我们公司现实的时候就只有一个打卡的按钮，点击了上班打卡的就显示下班打卡，然后每天定时统计当天的个人、部门、整个公司的考勤，隔天可以查询、对有异常的进行申请申诉、自动提醒至rtx，领导的审批，请假之前的申请等····
------
学习学习！！！！
------
汗~，这不是我们实验室的打卡系统么，但貌似最后大家懒得算了，啥子请假，啥子上课诸多情况~
------
汗~，这不是我们实验室的打卡系统么，但貌似最后大家懒得算了，啥子请假，啥子上课诸多情况
------
学习学习。。

------
这东西烦了我三年，终于解脱了！
考勤情况实在太多，能处理好不容易。
LZ一条语句搞不定，就多搞几次吧。
------
bu错
------
这样的SQl语句我还真没信心写出来，学习了。
------
SQL Server数据格式化工具
------
SQL Server数据格式化工具
------
...........
------
mark~~~~~~``
------
其余每天的任何时间段，update后，type值变为“乱刷卡”

还有其他时间吗？
------
好东西，学习了！！！
------
学习学习~~~~~~~~~~~~~~~~~~~~~
------
学习，学习啊
------
学习……
------
学习学习
------
我不是很懂　学习一下呵呵
------
就是为了弄点分...
------
我不是很懂　学习一下
------
其实我在想， 若在今日7：30分刷卡了， 直到23：30分第二次刷卡或是到第二天7：30分第二次刷卡， 这个要怎么算？
------
学习了
------
学习了，。。。
------
数据量大，不知效果如何
------
收获点果实。。
------
CSDN向来很强大的 哈哈
貌似 楼主忘了结贴了- -
------
你这个没考虑夜班情况。
------
我是来赚分的
------
再来一下
------
再来一下
------
学习学习....
------
问下啊，为什么你们写的程序都带“--------------------SQL Server数据格式化工具-------------------这个呢？
”
------
学习学习，这个需求什么的很多情况，靠机器解决有点难，仍然需要人工判断。
------
学习SQL ~~~~~~~~~~~
------
--根据3楼的改编为SQL2000的，接触SQL不多，没考虑效率方面

update attendance set type=t3.type
from
attendance tt
inner join
(select t1.name name, t1.time time, type=case when t2.type is not null then t2.type
 when t2.type is null and convert(varchar(10),t1.time,108)<'12:00' then '上班重复刷卡'
 when t2.type is null and convert(varchar(10),t1.time,108)>'13:00' then '下班重复刷卡' end
from
attendance t1
left join
(select t.name,t.time,type=case when convert(varchar(10),t.time,108)<'08:00' and idd=1 then '上班'
when convert(varchar(10),t.time,108)>'08:00' and idd=1 then '迟到'
when convert(varchar(10),t.time,108)>'18:30' and idd=2 then '下班'
when convert(varchar(10),t.time,108)<'18:30' and idd=2 then '早退'
else '乱刷卡' end from
(select name,min(time) as time,'1' idd 
from attendance 
where convert(varchar(10),time,108)<'12:00' 
group by name,convert(varchar(10),time,120)
union all
select name,max(time) as time,'2' idd
from attendance
where convert(varchar(10),time,108)>'13:00'
group by name,convert(varchar(10),time,120)
union all
select name,time,'3' idd
from attendance 
where convert(varchar(10),time,108)>='12:00' and convert(varchar(10),time,108)<='13:00') t) t2
on t1.name=t2.name and t1.time=t2.time) t3
on tt.name=t3.name and tt.time=t3.time
------
下班时间改为了18:30，为了测试，17:30没有早退，2楼的答案有点出入
------
楼主，分太少了……
------
学习!路过，路过！
------
http://www.intik.cn
------
这里发言，表示您接受了CSDN社区的用户行为准则。 

------
没错！很好！
------
谢谢了

------
学习了，最近正在做类似的系统
桂ICP备07017180号