用友U8存货月采购平均单价,存货在当月的总采购金额除以总采购数量,计算出改存货的月平均采购价格。
SELECT Temp.cInvCode,
Temp.iQuantity,
Temp.iSum,
Temp.iSum / Temp.iQuantity AS price,
Temp.iYear,
Temp.iPeriod
FROM
(
SELECT cInvCode,
SUM(ISNULL(A.iQuantity, 0)) AS iQuantity,
SUM(ISNULL(A.iSum, 0)) AS iSum,
A.iYear,
A.iPeriod
FROM
(
SELECT YEAR(dPODate) AS iYear,
MONTH(dPODate) AS iPeriod,
cInvCode,
iQuantity,
iSum
FROM dbo.PO_Pomain
JOIN dbo.PO_Podetails
ON PO_Podetails.POID = PO_Pomain.POID
AND iSum IS NOT NULL
) A
GROUP BY A.cInvCode,
A.iYear,
A.iPeriod
) Temp
ORDER BY Temp.iYear,
Temp.iPeriod DESC