又一个查询，哥整了一天，NND有点受不鸟了

--参考表--
With RM(MPartNo,MPartName,UM,MSupplier,Stock,MType)
as
( 
 select 
 p_partno MPartNo,p_partname MPartName,p_unit UM,p_supplier MSupplier,
 Stock,p_type MType
 from p_partno_rm
), --计算期初库存----
 RMBeforeQty_tmp(MPartNo, BefQty)
as
(
  --电线部分统计
  select  MPartNo, 
  Sum(SumInQty)-Sum(SumOutQty)BefQty
  From(
	   Select partno MPartNo,
	   Sum(isnull(inQty,0)) * 1000  SumInQty,
	   0 SumOutQty From Storage_Cable 
	   Where Indate <'$BDate'
	   group by partno
       --如果电线发料信息在storage_fixbin中出现,调整下面代码
	   Union all

	   Select partno MPartNo,
	   0 SumInQty,
	   Sum(isnull(outQty,0)) * 1000  SumOutQty From Storage_Cable 
	   Where outdate <'$BDate'
	   group by partno
      ) as t_cable_befQty
      group by MPartNo
  Union all
  --其他材料-----------------------
  select  MPartNo, 
  Sum(SumInQty)-Sum(SumOutQty)BefQty
  From(
	  select partno MPartNo,
	  sum(isnull(inqty,0)) SumInQty,
	  0 SumOutQty
	  From Storage_RM 
	  Where TicketType <> '归还' 
	  And Indate <'$BDate'
	  Group by partno

	  Union all
      --这里需要考虑排除电线
	  select partno MPartNo,
	  0 SumInQty,
	  sum(isnull(outqty,0)) SumOutQty
	  From Storage_fixBin
	  Where ptype = '发料'  And TicketStatus <> '借用'
	  And outdate <'$BDate'
	  Group by partno

   ) as t_rm_befQty
   group by MPartNo

   
), --再次汇总求期初库存
RMBefQty(MPartNo,BefQty)
as
(
  Select MPartNo, Sum(BefQty) BefQty From RMBeforeQty_tmp
  group by MPartNo

), --电线入库统计
CableIn(MPartNo,ProductionIn,BuyIn,InventoryIn,OtherIn,InventoryProfitIn,ReturnIn)
as
(
   Select partno MPartNo,
   Sum(isnull(inQty,0)) * 1000  ProductionIn,
   0 BuyIn, 0 InventoryIn, 0 OtherIn, 0 InventoryProfitIn, 0 ReturnIn
   From Storage_Cable 
   Where Indate >='$BDate' and indate <='$EDate 23:59:59'
   group by partno

),--电线出库统计
CableOut (MPartNo,PlanOut,SellOut,OutPlanOut,
          InventoryLossesOut,BadMReturnOut,BorrowOut,OtherOut)
as
(
   Select partno MPartNo,
   Sum(isnull(outQty,0)) * 1000  PlanOut, 
   0 SellOut,0 OutPlanOut,0 InventoryLossesOut,0 BadMReturnOut,0 BorrowOut,0 OtherOut
   From Storage_Cable 
   Where outdate >='$BDate' and outdate <='$EDate 23:59:59'
   group by partno
),
--材料入库
RMIn(MPartNo,ProductionIn,BuyIn,InventoryIn,OtherIn,InventoryProfitIn,ReturnIn)
as
(

   --其他材料---
   Select MPartNo,
   Sum(ProductionIn) ProductionIn,
   Sum(BuyIn) BuyIn,
   Sum(InventoryIn)InventoryIn,
   Sum(OtherIn)OtherIn, 
   Sum(InventoryProfitIn) InventoryProfitIn,
   Sum(ReturnIn)ReturnIn
   From(
	   Select partno MPartNo,
	   Case When tickettype='生产缴库' Then isnull(inQty,0)
			Else 0 End as ProductionIn,
	   Case When tickettype='采购入库' Then isnull(inQty,0)
			Else 0 End as BuyIn,
	   Case When tickettype='盘存入库' Then isnull(inQty,0)
			Else 0 End as InventoryIn,
	   Case When tickettype='其它入库' Then isnull(inQty,0)
			Else 0 End as OtherIn,
	   Case When tickettype='盘盈入库' Then isnull(inQty,0)
			Else 0 End as InventoryProfitIn,
	   Case When tickettype='归还' Then isnull(inQty,0)
			Else 0 End as ReturnIn
	   From Storage_RM
	   Where 
	   Indate >='$BDate' and indate <='$EDate 23:59:59'
   )as t
   group by MPartNO
  

), --材料出库
RMOut(MPartNo,PlanOut,SellOut,OutPlanOut,
InventoryLossesOut,BadMReturnOut,BorrowOut,OtherOut)
as
(
   Select MPartNo,
   Sum(PlanOut) PlanOut,
   Sum(SellOut) SellOut,
   Sum(OutPlanOut) OutPlanOut,
   Sum(InventoryLossesOut) InventoryLossesOut,
   Sum(BadMReturnOut) BadMReturnOut,
   Sum(BorrowOut) BorrowOut,
   Sum(OtherOut) OtherOut
   From(
	   Select partno MPartNo,
	   Case When ticketstatus='生产性领料' Then isnull(OutQty,0)
			Else 0 End as PlanOut,
	   Case When ticketstatus='销售出库' Then isnull(OutQty,0)
			Else 0 End as SellOut,
	   Case When ticketstatus='计划外出库' Then isnull(OutQty,0)
			Else 0 End as OutPlanOut,
	   Case When ticketstatus='盘亏出库' Then isnull(OutQty,0)
			Else 0 End as InventoryLossesOut,
	   Case When ticketstatus='废料退货' Then -isnull(InQty,0)
			Else 0 End as BadMReturnOut,
	   Case When ticketstatus='借用' Then isnull(OutQty,0)
			Else 0 End as BorrowOut,
	   Case When ticketstatus='其它出库' Then isnull(OutQty,0)
			Else 0 End as OtherOut
	   
	   From Storage_Fixbin
	   Where ptype = '发料' 
	   And Outdate >='$BDate' and Outdate <='$EDate 23:59:59'
   ) as t
   group by MPartNo


),--入库集合
SetIn(MPartNo,ProductionIn,BuyIn,InventoryIn,OtherIn,InventoryProfitIn,ReturnIn)
as
(
  Select * From CableIn
  Union
  Select * From RMIn

),--出库集合
SetOut(MPartNo,PlanOut,SellOut,OutPlanOut,
InventoryLossesOut,BadMReturnOut,BorrowOut,OtherOut)
as
(
  Select * From CableOut
  Union
  Select * From RMOut

)

Select *,
ProductionIn + BuyIn+ InventoryIn +OtherIn +InventoryProfitIn As SumIn,
PlanOut + SellOut + OutPlanOut + InventoryLossesOut +BadMReturnOut +OtherOut As SumOut,
BefQty+ProductionIn + BuyIn+ InventoryIn +OtherIn +InventoryProfitIn - (PlanOut + SellOut + OutPlanOut + InventoryLossesOut +BadMReturnOut +OtherOut)As Inventory
From
(
	select RM.*,BefQty,
	isnull(ProductionIn,0) ProductionIn,
	isnull(BuyIn,0) BuyIn,
	isnull(InventoryIn,0) InventoryIn,
	isnull(OtherIn,0) OtherIn,
	isnull(InventoryProfitIn,0) InventoryProfitIn,
	isnull(ReturnIn,0) ReturnIn,
	isnull(PlanOut,0) PlanOut,
	isnull(SellOut,0) SellOut,
	isnull(OutPlanOut,0) OutPlanOut,
	isnull(InventoryLossesOut,0) InventoryLossesOut,
	isnull(BadMReturnOut,0) BadMReturnOut,
	isnull(BorrowOut,0) BorrowOut,
	isnull(OtherOut,0) OtherOut
    from 
	RM left join RMBefQty On RM.MPartNo=RMBefQty.MPartNo
	   left join SetIn  On RM.MPartNo=SetIn.MPartNo
	   left join SetOut On RM.MPartNo=SetOut.MPartNo
) as t
Where 1=1
$Condition