U8库存批次单位成本

with inv_history as (
	SELECT
	*
	FROM (
		SELECT
		inv_pos.cvouchtype,
		inv_pos.cWhCode,
		inv_pos.cPosCode,
		inv_pos.cInvCode,
		inv_pos.cBatch,
		inv_pos.cFree1,
		rd01_d.iQuantity,
		rd01_d.iPrice,
		rd01_d.iPrice / rd01_d.iQuantity unit_price,
		case when iPOsID is null then rd01_d.iQuantity else sum(rd01_d.iQuantity) over(partition by iPOsID) end total_qty
		FROM rdrecords01 rd01_d
		INNER JOIN [dbo].[InvPosition] inv_pos
		ON rd01_d.AutoID = inv_pos.RdsID
		AND inv_pos.cvouchtype = '01'
	) tmp
	WHERE tmp.total_qty>0

union

	SELECT
	inv_pos.cvouchtype,
	inv_pos.cWhCode,
	inv_pos.cPosCode,
	inv_pos.cInvCode,
	inv_pos.cBatch,
	inv_pos.cFree1,
	rd_d.iQuantity,
	case rd_d.iPrice when 0 then COALESCE(rd_d.cDefine26/rd_d.iQuantity, 0) else rd_d.iPrice end iPrice,
	case rd_d.iPrice when 0 then COALESCE(rd_d.cDefine26/rd_d.iQuantity, 0) else rd_d.iPrice end / rd_d.iQuantity unit_price,
	rd_d.iQuantity total_qty

	FROM [dbo].[InvPosition] inv_pos
	LEFT JOIN rdrecords34 rd_d
	ON rd_d.AutoID = inv_pos.RdsID
	WHERE inv_pos.cvouchtype = '34'
	AND inv_pos.iQuantity > 0

union

	SELECT
	inv_pos.cvouchtype,
	inv_pos.cWhCode,
	inv_pos.cPosCode,
	inv_pos.cInvCode,
	inv_pos.cBatch,
	inv_pos.cFree1,
	rd_d.iQuantity,
	case rd_d.iPrice when 0 then COALESCE(rd_d.cDefine26/rd_d.iQuantity, 0) else rd_d.iPrice end iPrice,
	case rd_d.iPrice when 0 then COALESCE(rd_d.cDefine26/rd_d.iQuantity, 0) else rd_d.iPrice end / rd_d.iQuantity unit_price,
	rd_d.iQuantity total_qty

	FROM [dbo].[InvPosition] inv_pos
	LEFT JOIN rdrecords08 rd_d
	ON rd_d.AutoID = inv_pos.RdsID
	WHERE inv_pos.cvouchtype = '08'
	AND inv_pos.iQuantity > 0
)

-- SELECT * FROM inv_history

SELECT
cInvCode,
cBatch,
cFree1,
count(distinct cPosCode) position_count,
count(*) stock_count,
min(unit_price) min_unit_price,
max(unit_price) max_unit_price,
avg(unit_price) unit_price
FROM inv_history

GROUP BY cInvCode, cBatch, cFree1