中控考勤数据转换
把每个人当天的多条考勤记录,汇总成 当天的一条记录。汇总格式如: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
你的一分支持,是我坚持创作的十分动力。
如果文章的内容对你有帮助的话,请用微信扫描下方二维码,支持一下。谢谢!