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

--------------------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 行受影响)

*/