sanlu_new/sql/库存查询.sql


	SELECT G.*, ISNULL(H.OnShipQty_Product, 0) AS OnShipQty_Product FROM
	(
	Select Product.ID_Product, Product.No_Product,Name_Product,Price_Product,IsNull(A.AvailableQty_ProductStocks,0) As AvailableQty_ProductStocks,IsNull(B.StockQty_ProductStocks,0) As StockQty_ProductStocks,IsNull(C.Quantity_NoSaleOutDepot,0) As Quantity_NoSaleOutDepot,IsNull(D.Quantity_NoSaleRtnInDepot,0) As Quantity_NoSaleRtnInDepot,IsNull(E.Quantity_NoPurchaseInDepot,0) As Quantity_NoPurchaseInDepot
	From Product
	Left Join
	(
		Select ID_Product,Sum(AvailableQty_ProductStocks) As AvailableQty_ProductStocks
		From ProductStocks
		Join Location On Location.ID_Location=ProductStocks.ID_Location
		Where Level_Location=0
		Group BY ID_Product
	)A On A.ID_Product=Product.ID_Product
	Left Join 
	(
		Select ID_Product,Sum(StockQty_ProductStocks) As StockQty_ProductStocks
		From ProductStocks
		Join Location On Location.ID_Location=ProductStocks.ID_Location
		Where Level_Location=1
		Group BY ID_Product
	)B On B.ID_Product=Product.ID_Product
	Left Join 
	(
		Select SaleBillProduct.ID_Product,Sum(Quantity_Sale-Quantity_OutDepot) As Quantity_NoSaleOutDepot
		From SaleSourceBillProduct
		Join SaleBillProduct On SaleSourceBillProduct.ID_SaleSourceBillProduct=SaleBillProduct.ID_SaleBillProduct
		Where (Quantity_Sale-Quantity_OutDepot)>0
		Group By SaleBillProduct.ID_Product
	)C On C.ID_Product=Product.ID_Product
	Left Join
	(
		Select SaleBillProduct.ID_Product,Sum(Quantity_SaleRtn-Quantity_InDepot) As Quantity_NoSaleRtnInDepot
		From SaleSourceBillProduct
		Join SaleBillProduct On SaleSourceBillProduct.ID_SaleSourceBillProduct=SaleBillProduct.ID_SaleBillProduct
		Where (Quantity_SaleRtn-Quantity_InDepot)>0
		Group By SaleBillProduct.ID_Product
	)D On D.ID_Product=Product.ID_Product
	Left Join
	(
		Select po.ID_Product,Sum(ps.Quantity_Order-ps.Quantity_InDepot-ps.Quantity_EndCase) As Quantity_NoPurchaseInDepot
		From PurchaseSourceBillProduct ps
		Join PurchaseOrderBillProduct po On po.ID_PurchaseOrderBillProduct=ps.ID_PurchaseSourceBillProduct
		Where (ps.Quantity_Order-ps.Quantity_InDepot-ps.Quantity_EndCase)>0
		Group BY po.ID_Product
	)E On E.ID_Product=Product.ID_Product
	Where No_Product between IsNull(@StartNo_Product,No_Product) And IsNull(@EndNo_Product,No_Product)
	And Name_Product between IsNull(@StartName_Product,Name_Product) And IsNull(@EndName_Product,Name_Product)
	And Type_Product between IsNull(@StartType_Product,Type_Product) And IsNull(@EndType_Product,Type_Product)
	And (A.AvailableQty_ProductStocks Is Not Null Or B.StockQty_ProductStocks Is Not Null Or C.Quantity_NoSaleOutDepot Is Not Null Or D.Quantity_NoSaleRtnInDepot Is Not Null Or E.Quantity_NoPurchaseInDepot Is Not Null)
	--Order By Product.Type_Product, Product.Name_Product
	)G
	left join
	(
	select ID_Product, SUM(ISNULL(Quantity_OnShipProduct, 0)) AS OnShipQty_Product from  OnShipProduct 
	GROUP BY ID_Product
	) H ON G.ID_Product = H.ID_Product
																							
	,@StartNo_Product nvarchar(64),@EndNo_Product nvarchar(64),@StartType_Product nvarchar(64),@EndType_Product nvarchar(64),@StartName_Product nvarchar(64),@EndName_Product nvarchar(64),@StartNo_Product=N'12345',@EndNo_Product=NULL,@StartType_Product=NULL,@EndType_Product=NULL,@StartName_Product=NULL,@EndName_Product=NULL