库存汇总报表（只限采购收货+采购退货+销售交货+销售退货）

代码示例

SELECT T0.[ItemCode],  T1.[ItemName],T1.[FrgnName] AS'型号',Ta.qty AS '期初库存',A1.QC AS'期初金额'
	, Tc.qty  AS '本期入库数量',A0.RK AS'期间入库金额', Td.qty  AS '本期出库数量', A0.CK AS'期间出库金额', Tb.qty AS '期末库存数量',ISNULL(A1.QC,0) +ISNULL(A2.QM,0) AS'期末金额'
FROM OIVL T0 INNER JOIN OITM T1 ON T0.[ItemCode] = T1.[ItemCode]
	LEFT JOIN (
		SELECT T0.[ItemCode],SUM(T0.[InQty] - T0.[OutQty]) AS 'qty'
		FROM OIVL T0
		WHERE T0.[DocDate] <[%0] AND T0.[TransType] IN(20,21,15,16)
		GROUP BY T0.[ItemCode]
	) Ta ----期初库存
	ON Ta.itemcode = t0.itemcode

	LEFT JOIN ( 
		SELECT T0.[ItemCode], SUM(T0.[InQty] - T0.[OutQty]) AS 'qty'
		FROM OIVL T0
		WHERE T0.[DocDate] <=[%1] AND T0.[TransType] IN(20,21,15,16)
		GROUP BY T0.[ItemCode]
	) Tb ----期末库存
	ON Tb.itemcode = t0.itemcode
	LEFT JOIN ( 
		SELECT T0.[ItemCode], SUM(T0.[InQty]) AS 'qty'
		FROM OIVL T0
		WHERE T0.[DocDate] >=[%0]  AND T0.[DocDate] <=[%1]  AND T0.[TransType] IN(20,16)
		GROUP BY T0.[ItemCode]
	) Tc ----期间入库
	ON Tc.itemcode = t0.itemcode
	LEFT JOIN ( 
		SELECT T0.[ItemCode], SUM(T0.[OutQty]) AS 'qty'
		FROM OIVL T0
		WHERE T0.[DocDate] >=[%0]  AND T0.[DocDate] <=[%1] AND T0.[TransType] IN(21,15)
		GROUP BY T0.[ItemCode]
	) Td ----期间出库
	ON Td.itemcode = t0.itemcode

----计算期间库存金额
LEFT JOIN
(
SELECT T0.ItemCode,SUM(ISNULL(T1.[GTotal],0)+ISNULL(T4.[GTotal],0)) AS'RK',SUM(ISNULL(T2.[GTotal],0)+ISNULL(T3.[GTotal],0)) AS'CK'
FROM OIVL T0
	LEFT JOIN PDN1 T1
	ON T0.[BASE_REF] = T1.[DocEntry]
		AND T0.[DocLineNum] = T1.[LineNum]
		AND T1.ObjType = T0.[TransType]
	LEFT JOIN RPD1 T2
	ON T0.[BASE_REF] = T2.[DocEntry]
		AND T0.[DocLineNum] = T2.[LineNum]
		AND T2.ObjType = T0.[TransType]
	LEFT JOIN DLN1 T3
	ON T0.[BASE_REF] = T3.[DocEntry]
		AND T0.[DocLineNum] = T3.[LineNum]
		AND T3.ObjType = T0.[TransType]
	LEFT JOIN RDN1 T4
	ON T0.[BASE_REF] = T4.[DocEntry]
		AND T0.[DocLineNum] = T4.[LineNum]
		AND T4.ObjType = T0.[TransType]
WHERE  T0.[DocDate] >=[%0]  AND T0.[DocDate] <=[%1] AND T0.[TransType] IN(20,21,15,16)
GROUP BY T0.ItemCode
) A0 ON A0.ItemCode=T0.ItemCode

----计算期初金额
	LEFT JOIN (
		SELECT T0.ItemCode
			, SUM(ISNULL(T1.[GTotal], 0) + ISNULL(T4.[GTotal], 0) - ISNULL(T2.[GTotal], 0) - ISNULL(T3.[GTotal], 0)) AS 'QC'
		FROM OIVL T0
			LEFT JOIN PDN1 T1
			ON T0.[BASE_REF] = T1.[DocEntry]
				AND T0.[DocLineNum] = T1.[LineNum]
				AND T1.ObjType = T0.[TransType]
			LEFT JOIN RPD1 T2
			ON T0.[BASE_REF] = T2.[DocEntry]
				AND T0.[DocLineNum] = T2.[LineNum]
				AND T2.ObjType = T0.[TransType]
			LEFT JOIN DLN1 T3
			ON T0.[BASE_REF] = T3.[DocEntry]
				AND T0.[DocLineNum] = T3.[LineNum]
				AND T3.ObjType = T0.[TransType]
			LEFT JOIN RDN1 T4
			ON T0.[BASE_REF] = T4.[DocEntry]
				AND T0.[DocLineNum] = T4.[LineNum]
				AND T4.ObjType = T0.[TransType]
		WHERE T0.[DocDate] < [%0]
			AND T0.[TransType] IN (20, 21, 15, 16)
		GROUP BY T0.ItemCode
	) A1
	ON A1.ItemCode = T0.ItemCode

----计算期末金额
	LEFT JOIN (
		SELECT T0.ItemCode
			, SUM(ISNULL(T1.[GTotal], 0) + ISNULL(T4.[GTotal], 0) - ISNULL(T2.[GTotal], 0) - ISNULL(T3.[GTotal], 0)) AS 'QM'
		FROM OIVL T0
			LEFT JOIN PDN1 T1
			ON T0.[BASE_REF] = T1.[DocEntry]
				AND T0.[DocLineNum] = T1.[LineNum]
				AND T1.ObjType = T0.[TransType]
			LEFT JOIN RPD1 T2
			ON T0.[BASE_REF] = T2.[DocEntry]
				AND T0.[DocLineNum] = T2.[LineNum]
				AND T2.ObjType = T0.[TransType]
			LEFT JOIN DLN1 T3
			ON T0.[BASE_REF] = T3.[DocEntry]
				AND T0.[DocLineNum] = T3.[LineNum]
				AND T3.ObjType = T0.[TransType]
			LEFT JOIN RDN1 T4
			ON T0.[BASE_REF] = T4.[DocEntry]
				AND T0.[DocLineNum] = T4.[LineNum]
				AND T4.ObjType = T0.[TransType]
		WHERE T0.[DocDate] >= [%0]  AND T0.[DocDate] <= [%1] 
			AND T0.[TransType] IN (20, 21, 15, 16)
		GROUP BY T0.ItemCode
	) A2
	ON A2.ItemCode = T0.ItemCode

WHERE T0.[DocDate] >=[%0] AND T0.[DocDate] <=[%1]
GROUP BY T0.[ItemCode],T1.[ItemName],T1.[FrgnName] ,Ta.qty,Tb.qty, Tc.qty , Td.qty,A0.RK,A0.CK,A1.QC,A2.QM