查询未清交货单成本（移动平均模式）

示例代码

DECLARE @DocDate datetime
DECLARE @tmp int
SELECT @tmp = COUNT(*)
FROM ODLN T9
WHERE T9.[DocDate] <= '[%0]'
IF '[%0]' <> ''
	SET @DocDate = '[%0]'
SELECT 成本科目, 科目名称, 未结转金额, 内部编号, 单据号
	, 过账日期, 客户, 客户名称, 物料号, 描述
	, 交货数量, 交货仓库, 未开票数量, 单位成本, 总额
FROM (
	SELECT t0.CogsAcct AS 成本科目, t1.AcctName AS 科目名称, t0.TotalCost AS 未结转金额, N'详细信息：' AS 内部编号, NULL AS 单据号
		, NULL AS 过账日期, NULL AS 客户, NULL AS 客户名称, NULL AS 物料号, NULL AS 描述
		, NULL AS 交货数量, NULL AS 交货仓库, NULL AS 未开票数量, NULL AS 单位成本, t0.CogsAcct AS indx
		, NULL AS 总额
	FROM (
		SELECT CogsAcct, SUM(StockPrice * OpenQty) AS TotalCost
		FROM DLN1
			INNER JOIN odln ON DLN1.DocEntry = ODLN.DocEntry
		WHERE (odln.DocDate <= @DocDate
				OR @DocDate IS NULL)
			AND OpenQty <> 0
		GROUP BY CogsAcct
	) t0
		LEFT JOIN OACT t1 ON t0.CogsAcct = t1.AcctCode
	UNION ALL
	SELECT NULL AS Expr1, NULL AS Expr2, NULL AS Expr3, CAST(t2.DocEntry AS nvarchar(20)) AS Expr4, t3.DocNum
		, t3.DocDate, t3.CardCode, t3.CardName, t2.ItemCode, t2.Dscription
		, t2.Quantity, t2.WhsCode, t2.OpenQty, t2.StockPrice
		, t2.CogsAcct + CAST(t2.DocEntry AS nvarchar(20)) AS Expr5, t2.StockPrice * t2.OpenQty
	FROM DLN1 t2
		INNER JOIN ODLN t3 ON t2.DocEntry = t3.DocEntry
	WHERE (t3.DocDate <= @DocDate
			OR @DocDate IS NULL)
		AND t2.OpenQty <> 0
) t4
ORDER BY indx