--当前统计日期，统计月份 


--------------------------------------------------------------------------------

select GETDATE() as 当前统计日期,convert(char(10),month(GETDATE()))+'月' as 统计月份 


--------------------------------------------------------------------------------

--统计当月认证户数 


--------------------------------------------------------------------------------

select count(distinct zhh) as 当月认证户数 from dkl where month(fssj)=month(getdate()) 


--------------------------------------------------------------------------------

--统计当月总体修改情况 


--------------------------------------------------------------------------------

select t2.zpc as 总共发送批次,t1.xgpc as 发生修改批次,convert(char(10),t1.xgpc/convert(float,t2.zpc)*100)+'%' as 修改比率 from (select count(distinct hzhm) as xgpc from dkl where rzjg in (01,02) and month(fssj)=month(getdate())) t1, (select count(distinct hzhm) as zpc from dkl where month(fssj)=month(getdate())) t2 


--------------------------------------------------------------------------------

--统计当月每操作员情况表 


--------------------------------------------------------------------------------

select '编号'+convert(char(10),ta.czybh) as 操作员代码,ta.a as 扫描发票数,tb.b as 一次认证通过,convert(char(10),(tb.b/CONVERT(float, ta.a))*100)+'%' as 一次通过率 from 

(select czybh,count(czybh) as a from dbo.DKL where rzjg in (00,01) --and czybh not in --('004','009','010') and month(lrsj)=month(GETDATE()) group by czybh) ta, (select czybh,count(czybh) as b from dbo.DKL where rzjg =00 and month(lrsj)=month(GETDATE()) group by czybh) tb 

where ta.czybh=tb.czybh order by 扫描发票数 desc 


--------------------------------------------------------------------------------

--合计 


--------------------------------------------------------------------------------

select '合计'as 合计,sum(tc.认证发票总数) as 扫描发票总数,sum(tc.一次认证通过) as 总一次认证通过,convert(char(10),(sum(tc.一次认证通过)/convert(float,sum(tc.认证发票总数))*100))+'%' as 平均一次通过率 from (select ta.czybh as 操作员代码,ta.a as 认证发票总数,tb.b as 一次认证通过,convert(char(10),(tb.b/CONVERT(float, ta.a))*100)+'%' as 一次通过率 from 

(select czybh,count(czybh) as a from dbo.DKL where rzjg in (00,01) --and czybh not in --('004','009','010') and month(lrsj)=month(GETDATE()) group by czybh) ta, (select czybh,count(czybh) as b from dbo.DKL where rzjg =00 and month(lrsj)=month(GETDATE()) group by czybh) tb 

where ta.czybh=tb.czybh) tc 


--------------------------------------------------------------------------------

--统计日平均等待时间 


--------------------------------------------------------------------------------

select t2.fsr as 发送日期,convert(char(2),(sum(t2.waittime)/count(t2.fsr))/60)+'分'+convert(char(2),(sum(t2.waittime)/count(t2.fsr))%60)+'秒' as 日平均等待时间 from (select day(t1.fssj) as fsr,t1.waittime as waittime from (select (czybh+fspc) as pch,fssj,sdsj,DATEDIFF ( second ,fssj,sdsj) as waittime from dkl where month(fssj)=month(getdate()) group by (czybh+fspc),fssj,sdsj) t1) t2 group by t2.fsr order by t2.fsr 


--------------------------------------------------------------------------------

--统计月平均等待时间 


--------------------------------------------------------------------------------

select convert(char(2),(sum(t1.waittime)/count(*))/60)+'分'+convert(char(2),(sum(t1.waittime)/count(*))%60)+'秒' as 月平均等待时间 from (select (czybh+fspc) as pch,fssj,sdsj,DATEDIFF ( second ,fssj,sdsj) as waittime from dkl where month(fssj)=month(getdate()) group by (czybh+fspc),fssj,sdsj) t1 


--------------------------------------------------------------------------------

--统计当月错误信息情况 


--------------------------------------------------------------------------------

select hz_cwxx as 错误信息,count(hz_cwxx) as 张数 from dbo.RZSFMX where hz_rzjg=02 and month(hz_rzrq)=month(getdate()) group by hz_cwxx order by 张数 desc 

取自"http://nbw.jdls.com.cn/wiki/index.php/%E4%BB%A3%E7%90%86%E7%89%88%E7%BD%91%E4%B8%8A%E8%AE%A4%E8%AF%81%E4%BB%A3%E7%90%86%E7%82%B9%E5%B8%B8%E7%94%A8%E6%9F%A5%E8%AF%A2%E7%BB%9F%E8%AE%A1%E8%AF%AD%E5%8F%A5"