库存收发存报表（含金额）

代码示例

DECLARE @dte1 DATETIME
DECLARE @dte2 DATETIME
DECLARE @wh NVARCHAR(20)
DECLARE @int INT
SELECT @int = COUNT(*)
FROM pch1 p0
WHERE p0.docdate >= '[%0]' AND p0.docdate <= '[%1]'
SET @dte1 = '[%0]'
SET @dte2 = '[%1]' 
SELECT @int = COUNT(*)
FROM owhs p2
WHERE p2.whscode = '[%3]'
SET @wh = '[%3]'
SELECT t0.itemcode , t1.itemname AS 物料描述, t0.warehouse AS 仓库代码, t1.invntryuom AS 基本单位, t0.bqty AS 期初数量
	, CASE 
		WHEN isnull(t0.bqty, 0) = 0 THEN 0
		ELSE t0.bsum / t0.bqty
	END AS 期初单价, t0.bsum AS 期初金额, t0.nInQty AS 收入数量
	, CASE 
		WHEN isnull(t0.nInQty, 0) = 0 THEN 0
		ELSE t0.nInSum / t0.nInQty
	END AS 收入单价, t0.nInSum AS 收入金额, t0.nOutQty AS 发出数量
	, CASE 
		WHEN isnull(t0.nOutQty, 0) = 0 THEN 0
		ELSE t0.nOutSum / t0.nOutQty
	END AS 发出单价, t0.nOutSum AS 发出金额, t0.eQty AS 结存数量
	, CASE 
		WHEN isnull(t0.eQty, 0) = 0 THEN 0
		ELSE t0.eSum / t0.eQty
	END AS 结存单价, t0.eSum AS 结存金额
FROM (
	SELECT OIVL.itemcode, OIVL.LocCode AS warehouse, SUM(CASE 
			WHEN OIVL.docdate <@dte1 THEN ivl1.LayerInQty - ivl1.LayerOutQ
			ELSE 0
		END) AS bQty
		, SUM(CASE 
			WHEN OIVL.docdate <@dte1 THEN ivl1.TransValue
			ELSE 0
		END) AS bSum, SUM(CASE 
			WHEN OIVL.docdate >= @dte1 THEN ivl1.LayerInQty
			ELSE 0
		END) AS nInQty
		, SUM(CASE 
			WHEN OIVL.docdate >= @dte1
			AND ivl1.LayerInQty > 0 THEN ivl1.TransValue
			ELSE 0
		END) AS nInSum, SUM(CASE 
			WHEN OIVL.docdate >= @dte1 THEN ivl1.LayerOutQ
			ELSE 0
		END) AS nOutQty
		, SUM(CASE 
			WHEN OIVL.docdate >= @dte1
			AND ivl1.LayerOutQ > 0 THEN ivl1.TransValue * -1
			ELSE 0
		END) AS nOutSum
		, SUM(ivl1.LayerInQty - ivl1.LayerOutQ) AS eQty
		, SUM(ivl1.TransValue) AS eSum
	FROM OIVL
		INNER JOIN IVL1 ON OIVL.TransSeq = IVL1.TransSeq
	WHERE OIVL.DocDate <= @dte2
		AND (OIVL.LocCode = @wh
			OR @wh = '')
	GROUP BY itemcode, OIVL.LocCode
) t0
	INNER JOIN oitm t1 ON t0.itemcode = t1.itemcode