库存成本偏差报表

• 逻辑：取库存过账清单单据的物料成本，取选择日期期间最高值成本和最低值成本，分析成本最高和最低相差交代的值即为问题值

示例代码

DECLARE @Bdate DATETIME
DECLARE @Edate DATETIME
SELECT @Bdate = T0.[DocDate]
FROM OIVL T0
WHERE T0.[DocDate] = '[%0]'
SELECT @Edate = T0.[DocDate]
FROM OIVL T0
WHERE T0.[DocDate] = '[%1]'
SELECT T0.[TransType], T0.[CreatedBy] AS '凭证单号', T0.[DocDate], T0.[ItemCode], T0.[LocCode]
	, T1.[CalcPrice] AS '物料成本'
	, CASE 
		WHEN T1.[CalcPrice] = TA.MIN THEN N'--' + T0.[LocCode] + N'仓--▲低值'
		WHEN T1.[CalcPrice] = TA.MAX THEN N'--' + T0.[LocCode] + N'仓--▼高值'
	END AS '极限值', TA.MIN AS '最低成本', TA.MAX AS '最高成本'
	, (TA.MAX - TA.MIN) / TA.MAX * 100 AS '高差比%'
FROM OIVL T0
	INNER JOIN IVL1 T1 ON T0.[TransSeq] = T1.[TransSeq]
	LEFT JOIN (
		SELECT T0.[ItemCode], T0.[LocCode], MAX(T1.[CalcPrice]) AS 'MAX'
			, MIN(T1.[CalcPrice]) AS 'MIN'
		FROM OIVL T0
			INNER JOIN IVL1 T1 ON T0.[TransSeq] = T1.[TransSeq]
		WHERE T0.[DocDate] >= @Bdate
			AND T0.[DocDate] <= @Edate
			AND T0.[TransType] NOT IN ('13', '18')
		GROUP BY T0.[ItemCode], T0.[LocCode]
	) TA
	ON TA.[ItemCode] = T0.[ItemCode]
		AND TA.[LocCode] = T0.[LocCode]
WHERE T0.[DocDate] >= @Bdate
	AND T0.[DocDate] <= @Edate
	AND T0.[TransType] NOT IN ('13', '18')
ORDER BY T0.[ItemCode], T0.[LocCode]