sql server 统计签到。
没什么比sql server好用的。
前一段,让我管理签到。zktime5.0。access2000数据库。
最开始写了个sql server存储过程。
1:先把数据导入sql server
2:运行一下存储过程
3:粘贴到excel,使用数据透析表。
打印出来完事。
CREATE TABLE jichu (bh int PRIMARY KEY,ri varchar(10) default(''),shxw varchar(30) default(''),sx varchar(4) default(''),jjr varchar(15) default(''))
CREATE TABLE dabiao (bh int,name char(10) default(''),ri varchar(10) default(''),shxw varchar(30) default(''),sx varchar(4) default(''),shij varchar(8) default(''),jjr varchar(15) default(''),chid int default(0),kuangg int default(0),qingj int default(0))
CREATE TABLE lianj (string varchar(1000))
CREATE TABLE shijian (shshk varchar(8),shshz varchar(8),shshj varchar(8),shxiak varchar(8),shxiaz varchar(8),shxiaj varchar(8),xiashk varchar(8),xiashz varchar(8),xiashj varchar(8),xiaxiak varchar(8),xiaxiaz varchar(8),xiaxiaj varchar(8))
CREATE TABLE CHECKINOUT (USERID int not null,CHECKTIME datetime not null)
CREATE TABLE USERINFO (USERID int not null,Name nvarchar(40) null)
运行脚本:
update jichu set sx=right(shxw,1)
update jichu set bh='' where bh is null
update jichu set jjr='' where jjr is null
declare @ksrq varchar(10),@jsrq varchar(10)
set @ksrq='2016-08-24'
set @jsrq='2016-08-31'
insert into qiandao (name,ri,shij) select b.name,left(CONVERT(varchar(50), a.checktime, 20),10) ri,right(CONVERT(varchar(50), a.checktime, 20),8) shij from checkinout a,userinfo b where a.userid=b.userid
delete qiandao where ri<@ksrq
delete qiandao where ri>@jsrq
update qiandao set sx=case when shij<='09:00:00' then '上'
when shij>'12:00:00' and shij<='16:00:00'then '下' end
insert xming (name) select DISTINCT name from qiandao
insert into dabiao (bh,name,ri,shxw,sx,jjr) select bh,name,ri,shxw,sx,jjr from xming,jichu
select name,ri,min(shij) shij,sx into #qian from qiandao where sx='上' or sx='下' group by name,ri,sx order by name,ri,shij
select a.bh,a.name,a.ri,a.shxw,a.sx,b.shij,a.jjr,a.chid,a.kuangg,a.qingj into #dabiao from dabiao a left join #qian b on a.ri=b.ri and a.name=b.name and a.sx=b.sx order by a.name,a.ri
update #dabiao set shij='' where shij is null
update #dabiao set chid=case when (shij>'08:00:00' and shij<='09:00:00') or (shij>'15:00:00' and shij<='16:00:00') then 1 else 0 end
update #dabiao set chid=0 where jjr like '%假日%'
update #dabiao set kuangg=case when shij='' then 1 else 0 end
update #dabiao set kuangg=0 where jjr like '%假日%'
select * from #dabiao order by name,ri,sx
drop table #qian
drop table #dabiao