- 常見問題
非強檢器具數量如何統計
發布時間：2020-07-28 信息來源： 瀏覽次數：次
1.創建存儲過程
2.系統參數設置進行對照
| 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.系統參數設置進行對照