查询当前企业VMI仓库期初数据，入库多少、领用多少和结存多少
declare @FTime date set @FTime='2019-10-01' declare @TTime date set @TTime='2019-10-10' declare @FSuppNumber varchar(30) set @FSuppNumber='C塑胶.04.1134' select FDate,b.FItemID,FStockID,b.FQty into #inQty from ICVMIInStock a inner join ICVMIInStockEntry b on a.FID=b.FID inner join t_Supplier c on a.FSupplyID=c.FItemID where c.FNumber=@FSuppNumber select FItemID,FStockID,sum(FQty) as FQty into #endQty from ICInventory where FStockID in (select distinct FStockID from #inQty ) group by FItemID,FStockID select z.* into #OutQty from ( select a.FDate,b.FItemID,case when b.FSCStockID=0 then b.FDCStockID else b.FSCStockID end as FStockID,b.FQty from ICStockBill a inner join ICStockBillEntry b on a.FInterID=b.FInterID where a.FCancellation=0 and a.FTranType in (21,24,28,29,43) and b.FQty<>0 and case when b.FSCStockID=0 then b.FDCStockID else b.FSCStockID end = (select FVMIStockID from t_Supplier where FNumber=@FSuppNumber) union all select a.FDate,b.FItemID,case when b.FSCStockID=0 then b.FDCStockID else b.FSCStockID end as FStockID,FQty from ICStockBill a inner join ICStockBillEntry b on a.FInterID=b.FInterID where a.FCancellation=0 and a.FTranType in (41) and b.FQty<>0 and case when b.FSCStockID=0 then b.FDCStockID else b.FSCStockID end = (select FVMIStockID from t_Supplier where FNumber=@FSuppNumber) ) z where FStockID in (select distinct FStockID from #inQty ) select a.FItemID,b.FNumber,b.FName,b.FModel,c.FName as FUnitName,a.FBeginQty,a.FInQty,a.FOutQty,(a.FBeginQty+a.FInQty-a.FOutQty) as FEndQty into #ztab from ( select isnull(x.FItemID,y.FItemID) as FItemID,isnull(x.FBeginQty,0) as FBeginQty,isnull(y.FInQty,0) as FInQty,isnull(y.FOutQty,0) as FOutQty from ( select isnull(a.FItemID,b.FItemID) as FItemID,isnull(a.FBeginQty,0)-isnull(b.FOutQty,0) as FBeginQty from ( select FItemID, sum(FQty) as FBeginQty from #inQty where FDate<@FTime group by FItemID ) a full join (select FItemID, sum(FQty) as FOutQty from #OutQty where FDate<@FTime group by FItemID) b on a.FItemID=b.FItemID ) x full join ( select isnull(a.FItemID,b.FItemID) as FItemID,isnull(a.FBeginQty,0) as FInQty,isnull(b.FOutQty,0) as FOutQty from ( select FItemID, sum(FQty) as FBeginQty from #inQty where FDate>=@FTime and FDate<=@TTime group by FItemID ) a full join (select FItemID, sum(FQty) as FOutQty from #OutQty where FDate>=@FTime and FDate<=@TTime group by FItemID) b on a.FItemID=b.FItemID) y on x.FItemID=y.FItemID ) a inner join t_ICItem b on a.FItemID=b.FItemID inner join t_MeasureUnit c on b.FUnitID=c.FItemID select cast(FItemID AS varchar(20)) as FItemID,FNumber,FName,FModel,FUnitName,FBeginQty as FBegQty,FInQty,FOutQty,FEndQty from #ztab union all select '','合计','','','',sum(FBeginQty),sum(FInQty),sum(FOutQty),sum(FEndQty) from #ztab drop table #endQty drop table #OutQty drop table #inQty drop table #ztab
