强检器具数量如何统计

发布时间：2020-07-28 信息来源：浏览次数：次

1.增加存储过程

create PROCEDURE [dbo].[GetQiangJianTJ]
   @QiJRQ varchar(10),
   @JieSRQ varchar(10)
AS
BEGIN
   SET NOCOUNT ON;
--生成预约情况统计临时表
   Create table #tmp01(
   序号 int identity,
   器具名称 varchar(40),
   数量 int
   )

   declare @QiJBH varchar(30),@QiJMC varchar(200),@QiJSL int,@QiJZSL int ,@QiJMC1 varchar(200)
   set @QiJSL=0
   set @QiJZSL=0

   --定义主游标
   declare cur_Tongj cursor for
   select QiJBH,QiJMC from QiJDM

   open cur_Tongj
   fetch next from cur_Tongj into @QiJBH,@QiJMC
   while @@fetch_status=0
   --select @QiJMC1=@QiJMC
   begin
   select @QiJMC1 =@QiJMC

   select @QiJSL=count(@QiJBH) from V_WeiTQJ where QiJBH=@QiJBH and ShouLRQ>=@QiJRQ
   and ShouLRQ<=@JieSRQ and WeiTFS like'%e-CQS系统网上预约%'

   select   @QiJZSL=@QiJZSL+@QiJSL
   if exists (select @QiJBH from V_WeiTQJ where QiJBH=@QiJBH and ShouLRQ>=@QiJRQ
   and ShouLRQ<=@JieSRQ and WeiTFS like'%e-CQS系统网上预约%')
   begin
   insert into #tmp01(器具名称,数量)
   values(@QiJMC,@QiJSL)
   end


   fetch next from cur_Tongj into @QiJBH,@QiJMC
   end
   close cur_Tongj
   deallocate cur_Tongj
   insert into #tmp01(器具名称,数量)
   values('总计：',@QiJZSL)
   select * from #tmp01
   drop table #tmp01
END

2.系统参数设置进行对照