SQL 筛选数据问题(异常打卡记录筛选)急！！

Question

把打卡记录从考勤机里导出后，
存在异常信息，
比如：有些人一天只有一条记录，有些人一天有三条记录(有两遍记录间隔时间很短，上午或下午都可能有相似纪录)，怎样把异常信息都筛选出来？数据例如：
姓名     编号     打卡时间
钱广 H033 2010-03-01 7:46:53
钱广 H033 2010-03-01 16:41:45
钱广 H033 2010-03-02 7:45:02
钱广 H033 2010-03-02 16:42:13
钱广 H033 2010-03-02 16:42:20
钱广 H033 2010-03-03 7:47:29
钱广 H033 2010-03-03 16:37:09
钱广 H033 2010-03-04 7:48:49
赵常 H053 2010-03-04 7:47:10
赵常 H053 2010-03-04 16:59:47
赵常 H053 2010-03-05 7:39:36
赵常 H053 2010-03-05 17:08:22
赵常 H053 2010-03-06 7:19:08
赵常 H053 2010-03-06 18:16:32
赵常 H053 2010-03-08 17:12:42
赵常 H053 2010-03-09 7:56:02
赵常 H053 2010-03-09 17:03:46
周青 S016 2010-03-01 7:30:45
周青 S016 2010-03-01 17:49:50
周青 S016 2010-03-02 7:31:00
周青 S016 2010-03-02 17:20:21
周青 S016 2010-03-03 7:32:32
周青 S016 2010-03-03 16:39:29
周青 S016 2010-03-04 7:09:39
周青 S016 2010-03-04 17:26:26
周青 S016 2010-03-05 7:13:07
周青 S016 2010-03-05 7:13:10
周青 S016 2010-03-05 16:31:14 

Answer 1

'给你个参考'人事考勤系统：多个时间段多笔可能重复打卡数据处理问题!/*请各位达人帮忙看下此问题，小弟初来乍到，不懂规矩的地方请各位达人海涵，问题详细如下：   
公司有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*/--------------------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--【SQL Server 2005 环境】--代码实现-->更新数据
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 行受影响)
*/
--【SQL Server 2000 环境】--代码实现
declare @temptb table(name char(5),id int,_time datetime,time varchar(5),type char(20),idd int)
-->更新数据
insert into @temptb
select name,id,_time=min(_time),time=min(time),type,idd from
(
select name,id,_time=time,time=convert(varchar(5),time,8),type,idd=1
    from attendance t where convert(varchar(5),time,8)<='12:00'
)t
group by name,id,convert(varchar(10),_time,120),type,idd
union all
select name,id,_time=min(_time),time=min(time),type,idd from
(
select name,id,_time=time,time=convert(varchar(5),time,8),type,idd=1
    from attendance t where convert(varchar(5),time,8)>='13:00'
)t
group by name,id,convert(varchar(10),_time,120),type,idd
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'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=2 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=2 then '下班重复刷卡' 
        when idd=0 then '乱刷卡' end
from
(
select * from @temptb
union all
select name,id,_time=time,time=convert(varchar(5),time,8),type,idd=2 from attendance t
where not exists (select 1 from @temptb where id=t.id and _time=t.time)
)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 行受影响)
*/

Answer 2

if object_id('attendance') is not null drop table attendance 
go
-- 创建数据表
create table attendance 
(
name char(20),
id char(10),
time datetime
)
go
--插入测试数据
insert into attendance 
select '钱广','H033','2010-03-01 7:46:53'
union all select '钱广','H033','2010-03-01 16:41:45'
union all select '钱广','H033','2010-03-02 7:45:02'
union all select '钱广','H033','2010-03-02 16:42:13'
union all select '钱广','H033','2010-03-02 16:42:20'
union all select '钱广','H033','2010-03-03 7:47:29'
union all select '钱广','H033','2010-03-03 16:37:09'
union all select '钱广','H033','2010-03-04 7:48:49'
union all select '赵常','H053','2010-03-04 7:47:10'
union all select '赵常','H053','2010-03-04 16:59:47'
union all select '赵常','H053','2010-03-05 7:39:36'
union all select '赵常','H053','2010-03-05 17:08:22'
union all select '赵常','H053','2010-03-06 7:19:08'
union all select '赵常','H053','2010-03-06 18:16:32'
union all select '赵常','H053','2010-03-08 17:12:42'
union all select '赵常','H053','2010-03-09 7:56:02'
union all select '赵常','H053','2010-03-09 17:03:46'
union all select '周青','S016','2010-03-01 7:30:45'
union all select '周青','S016','2010-03-01 17:49:50'
union all select '周青','S016','2010-03-02 7:31:00'
union all select '周青','S016','2010-03-02 17:20:21'
union all select '周青','S016','2010-03-03 7:32:32'
union all select '周青','S016','2010-03-03 16:39:29'
union all select '周青','S016','2010-03-04 7:09:39'
union all select '周青','S016','2010-03-04 17:26:26'
union all select '周青','S016','2010-03-05 7:13:07'
union all select '周青','S016','2010-03-05 7:13:10'
union all select '周青','S016','2010-03-05 16:31:14'----------------------------------------------------------------------------
-- 用下面的方法可以找到2分钟内重复打卡
select
b.name,
b.id,
b.time,
nexttime = (select top 1 a.time from attendance a where a.name = b.name and a.id = b.id and b.time < a.time and datediff(n,b.time,a.time) < 2)
from attendance b
order by b.name,b.id,b.time/* 
name                 id         time                    nexttime
-------------------- ---------- ----------------------- -----------------------
钱广                   H033       2010-03-01 07:46:53.000 NULL
钱广                   H033       2010-03-01 16:41:45.000 NULL
钱广                   H033       2010-03-02 07:45:02.000 NULL
钱广                   H033       2010-03-02 16:42:13.000 2010-03-02 16:42:20.000
钱广                   H033       2010-03-02 16:42:20.000 NULL
钱广                   H033       2010-03-03 07:47:29.000 NULL
钱广                   H033       2010-03-03 16:37:09.000 NULL
钱广                   H033       2010-03-04 07:48:49.000 NULL
赵常                   H053       2010-03-04 07:47:10.000 NULL
赵常                   H053       2010-03-04 16:59:47.000 NULL
赵常                   H053       2010-03-05 07:39:36.000 NULL
赵常                   H053       2010-03-05 17:08:22.000 NULL
赵常                   H053       2010-03-06 07:19:08.000 NULL
赵常                   H053       2010-03-06 18:16:32.000 NULL
赵常                   H053       2010-03-08 17:12:42.000 NULL
赵常                   H053       2010-03-09 07:56:02.000 NULL
赵常                   H053       2010-03-09 17:03:46.000 NULL
周青                   S016       2010-03-01 07:30:45.000 NULL
周青                   S016       2010-03-01 17:49:50.000 NULL
周青                   S016       2010-03-02 07:31:00.000 NULL
周青                   S016       2010-03-02 17:20:21.000 NULL
周青                   S016       2010-03-03 07:32:32.000 NULL
周青                   S016       2010-03-03 16:39:29.000 NULL
周青                   S016       2010-03-04 07:09:39.000 NULL
周青                   S016       2010-03-04 17:26:26.000 NULL
周青                   S016       2010-03-05 07:13:07.000 2010-03-05 07:13:10.000
周青                   S016       2010-03-05 07:13:10.000 NULL
周青                   S016       2010-03-05 16:31:14.000 NULL
*/----------------------------------------------------------------------------
-- 根据上面的结果用语句删除指定时间内重复打卡的记录
delete from attendance 
where exists(select 1 from (select b.name,b.id,nexttime = 
  (select top 1 a.time from attendance a where a.name = b.name and a.id = b.id and b.time < a.time and datediff(n,b.time,a.time) < 2) from attendance b) s 
  where s.name = attendance.name and s.id = attendance.id and s.nexttime = attendance.time)
/*
(2 行受影响)
*/
----------------------------------------------------------------------------
--限制最短重复打卡分钟数的"2" ，也可可以改为需要指定最短时间

Answer 3

不好意思，我讲的不清楚，
正常数据为一天两条记录，上班卡与下班卡。(他们每个人的上班时间和下班时间是不一样)
异常数据分为以下几种情况：
（1）一人一天只有一条记录,只有一条上班卡或一条下班卡；
（2）一人一天有两条记录,两遍上班卡无下班卡或两遍下班卡无上班卡；
（3）一人一天有两条以上的记录,就是多刷的纪录；
（4）提前走了或推后走了，有公事；
（5）还有，真的很搓，不上班来打个卡；我现在很郁闷的是：
打卡记录筛选出来了，工资怎么算？满勤天数
实到天数
未到天数
迟到次数
迟到分钟
早退次数
早退分钟
是否计算加班费（有些人加班有加班费，有些人没有）
加班时数
加班工资
基本工资
计件工资（有些人拿加班工资，又拿计件工资，要求最后的工资可以手动修改，加上计件工资后算出实发工资）
社保补助
其他津贴
应发工资
社保（负项）
个税（负项）
其他代扣
实发工资 这样的一个SQL能写吗？
 
happycell188 的结果看着蛮清楚的

Answer 4

一句SQL语句可能可以实现，但不建议采用，因为一句庞大的SQL代码维护起来很不方便！这需求蛮大的，建议专门找人来做...

Answer 5

当然不是几个SQL语句来完成的。几句话也说不完，说个大概吧，
1）有一个班次表，每个班次有它的应打卡时间，
2）对于加班，根据部门或人员要录入加班的应打卡时间。
3）实际打卡记录，你已经有了，
4）对于漏卡的，有一个签卡单录入，它其实也算实际打卡，
5）每天有一个日处理，其实就是根据应打卡时间和实际打卡时间运算出一个日结果表（用存储过程来运算）保存。
6）每月有一个月处理，就是根据日结果表算出月结果表保存。工资是通过月结果表算出来的。
日结果表体现出来每个人每天的考勤情况，你说的那些细节，都可以在存储过程中处理并在日结果表体现出来，如正班时数，加班时数，迟到、早退、调休、请假等信息。