非强检器具数量如何统计

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

1.创建存储过程

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

   declare @QiJMC varchar(200),@QiJSL int,@QiJZSL int
   set @QiJSL=0
   set @QiJZSL=0
   --定义主游标
   declare cur_Tongj cursor for
   select QiJMC from V_WeiTQJ group by QiJMC

   open cur_Tongj
   fetch next from cur_Tongj into @QiJMC
   while @@fetch_status=0
   begin

   select @QiJSL=sum(YangPSL) from V_WeiTQJ where QiJMC=@QiJMC and ShouLRQ>=@QiJRQ
   and ShouLRQ<=@JieSRQ and ISNULL(WeiTFS,'') <>'e-CQS系统网上预约'

   select @QiJZSL=sum(YangPSL) from V_WeiTQJ where ShouLRQ>=@QiJRQ
   and ShouLRQ<=@JieSRQ and ISNULL(WeiTFS,'') <>'e-CQS系统网上预约'


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

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

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