--參考表--
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 MTypefrom p_partno_rm
), --計算期初庫存----RMBeforeQty_tmp(MPartNo, BefQty)
as
(--電線部分統計select MPartNo, Sum(SumInQty)-Sum(SumOutQty)BefQtyFrom(Select partno MPartNo,Sum(isnull(inQty,0)) * 1000 SumInQty,0 SumOutQty From Storage_Cable Where Indate <'$BDate'group by partno--如果電線發料信息在storage_fixbin中出現,調整下面代碼Union allSelect partno MPartNo,0 SumInQty,Sum(isnull(outQty,0)) * 1000 SumOutQty From Storage_Cable Where outdate <'$BDate'group by partno) as t_cable_befQtygroup by MPartNoUnion all--其他材料-----------------------select MPartNo, Sum(SumInQty)-Sum(SumOutQty)BefQtyFrom(select partno MPartNo,sum(isnull(inqty,0)) SumInQty,0 SumOutQtyFrom Storage_RM Where TicketType <> '歸還' And Indate <'$BDate'Group by partnoUnion all--這里需要考慮排除電線select partno MPartNo,0 SumInQty,sum(isnull(outqty,0)) SumOutQtyFrom Storage_fixBinWhere ptype = '發料' And TicketStatus <> '借用'And outdate <'$BDate'Group by partno) as t_rm_befQtygroup by MPartNo), --再次匯總求期初庫存
RMBefQty(MPartNo,BefQty)
as
(Select MPartNo, Sum(BefQty) BefQty From RMBeforeQty_tmpgroup 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 ReturnInFrom 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 OtherOutFrom 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)ReturnInFrom(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 ReturnInFrom Storage_RMWhere Indate >='$BDate' and indate <='$EDate 23:59:59')as tgroup 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) OtherOutFrom(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 OtherOutFrom Storage_FixbinWhere ptype = '發料' And Outdate >='$BDate' and Outdate <='$EDate 23:59:59') as tgroup by MPartNo),--入庫集合
SetIn(MPartNo,ProductionIn,BuyIn,InventoryIn,OtherIn,InventoryProfitIn,ReturnIn)
as
(Select * From CableInUnionSelect * From RMIn),--出庫集合
SetOut(MPartNo,PlanOut,SellOut,OutPlanOut,
InventoryLossesOut,BadMReturnOut,BorrowOut,OtherOut)
as
(Select * From CableOutUnionSelect * 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) OtherOutfrom RM left join RMBefQty On RM.MPartNo=RMBefQty.MPartNoleft join SetIn On RM.MPartNo=SetIn.MPartNoleft join SetOut On RM.MPartNo=SetOut.MPartNo
) as t
Where 1=1
$Condition ?
快遞哥軟件，?
