中控考勤数据转换

把每个人当天的多条考勤记录，汇总成 当天的一条记录。汇总格式如：07:40;07:49;07:51;11:13;16:08;17:14。

考勤汇总按每个月生成一张表。kq2103list。格式如下。

gh                   badgenumber       name          days           week     checktimes
424-201008    1777                                  2021-03-19         1         08:12;17:24;16:43;08:30

干货如下，用的知识点是触发器，触发器用到 insert 批量插入，触发器的临时表就会有多条记录。所以还用到游标的知识。

alter Trigger tri_Check_insert On kqjl For Insert
AS 
--定义变量
 declare @CurMonth varchar(4), @name varchar(20)
 declare @gh VARCHAR(10),@kq_bh VARCHAR(10),@kq_name VARCHAR(20), @checktype VARCHAR(1), @verifycode VARCHAR(8), @sensorid VARCHAR(8),@flag VARCHAR(1),@id int
 DECLARE @checktime datetime
 declare @sql NVARCHAR(2000) 
 declare @sTable varchar(10)
    --在inserted表中查询已经插入记录信息  select @CurMonth =CurMonth,@kq_bh=badgenumber,@checktime=checktime,@checktype=checktype,@verifycode=verifycode,@sensorid=sensorid,@flag=flag,@id=id from inserted;
 declare cur_rankschange Cursor For select CurMonth,badgenumber,checktime,checktype,verifycode,sensorid,flag,id from inserted;   -- 这个是表
  
  --INSERT INTO dbo.kqjl_temp2([CurMonth],[gh],[badgenumber],[checktime],[machine_sn]) values(@CurMonth,@gh,@kq_bh,@checktime,@id)
 Open cur_rankschange 
 fetch next from  cur_rankschange into @CurMonth,@kq_bh,@checktime,@checktype,@verifycode,@sensorid,@flag,@id
     while @@fetch_status=0
 begin
 
 select @gh=gh,@name=name from HR.dbo.kqrs with (nolock) where badgenumber=@kq_bh
 
 if not exists (select 1 from kqjl with (nolock) where CurMonth=@CurMonth and gh=@kq_bh and id<@id and abs(datediff(ss,checktime,@checktime))<60)
 begin
 set @sTable='kq'+@CurMonth+'list'
 set @sql='if exists(select 1 from '+@sTable+' with (nolock) where gh='''+@gh+''' and [days]='''+CONVERT(varchar(10),@checktime,120)+''') ' 
 set @sql=@sql+'update '+@sTable+' WITH (ROWLOCK) set checktimes=checktimes+'';''+'''+CONVERT(varchar(5), @checktime, 114 )+''',[week]=1 where [days]='''+CONVERT(varchar(10),@checktime,120)+''' and gh='''+@gh+''' '
 set @sql=@sql+'else '
 set @sql=@sql+'insert into '+@sTable+'(gh,badgenumber,name,[days],checktimes,[week]) values('''+@gh+''','+@kq_bh+','''+@name+''','''+CONVERT(varchar(10),@checktime,120)+''','''+CONVERT(varchar(5),@checktime,114)+''',1)'
 exec(@sql)
end
--else
--begin
--  set @sTable='kq'+@CurMonth+'list'
--  set @sql='if not exists(select 1 from '+@sTable+' with (nolock) where gh='''+@gh+''' and [days]='''+CONVERT(varchar(10),@checktime,120)+''') ' 
--  set @sql=@sql+'insert into '+@sTable+'(gh,badgenumber,name,[days],checktimes,[week]) values('''+@gh+''','+@kq_bh+','''+@name+''','''+CONVERT(varchar(10),@checktime,120)+''','''+CONVERT(varchar(5),@checktime,114)+''',1)'
--  exec(@sql)
--end
fetch next from  cur_rankschange into @CurMonth,@kq_bh,@checktime,@checktype,@verifycode,@sensorid,@flag,@id   
end 
close cur_rankschange
deallocate cur_rankschange
go