SELECT I.item_id
	,COALESCE(D1.RecvQuant, 0) AS RecvQuant
	,COALESCE(D1.PndRcpt, 0) AS PndRcpt
	,COALESCE(D2.TTQTY, 0) AS TTQTY
	,COALESCE(D3.OpenPO, 0) AS OpenPO
FROM dbo.items I
	LEFT JOIN
	(
		SELECT R1.item_id
			,SUM(R1.received_quantity) AS RecvQuant
			,SUM(CASE WHEN R1.posted_flag = 'N' THEN R1.received_quantity END) AS PndRcpt
		FROM dbo.inventory_receipts_d R1
		GROUP BY R1.item_id
	) D1
		ON I.item_id = D1.item_id
	LEFT JOIN
	(
		SELECT A2.item_id
			,SUM(T2.stocking_quantity *
					CASE
						WHEN T2.transfer_type = 'F'
						THEN -1
						WHEN T2.transfer_type = 'T'
						THEN 1
					END
				) AS TTQTY
		FROM dbo.inventory_activity_d A2
			JOIN dbo.inventory_activity_transfer_d T2
				ON A2.transaction_no = T2.transaction_no
					AND A2.line_no = T2.line_no
		WHERE T2.move_from_to = 'W'
		GROUP BY A2.item_id
	) D2
		ON I.item_id = D2.item_id
	LEFT JOIN
	(
		SELECT S3.item_id
			,SUM(S3.stocking_quantity) AS OpenPO
		FROM dbo.po_sub_item_d S3
		WHERE S3.order_completion_date IS NULL
	) D3
		ON I.item_id = D3.item_id
WHERE I.record_status <> 'D'
	AND I.primary_item_category_no <> 'NONIN'
ORDER BY item_id

quote:

---

Originally posted by actsql

I removed the internal ORDER BY but I am getting the error "Msg 156, Level 15, State 1, Line 78 Incorrect syntax near the keyword 'ORDER'."

Here is the revised code:

SELECT item_id, RecvQuant, PndRcpt, TTQTY, OpenPO, [RecvQuant]-[PndRcpt]-[TTQTY] AS FinQty

FROM (

SELECT DISTINCT
t.item_id,


ISNULL((SELECT DISTINCT sum(dbo.inventory_receipts_d.received_quantity)
FROM dbo.inventory_receipts_d
WHERE dbo.inventory_receipts_d.item_id = t.item_id),0) AS RecvQuant,

ISNULL((SELECT DISTINCT sum(ISNULL(dbo.inventory_receipts_d.received_quantity,0))
FROM dbo.inventory_receipts_d
WHERE dbo.inventory_receipts_d.posted_flag = 'N'
AND dbo.inventory_receipts_d.item_id = t.item_id),0) AS PndRcpt,

ISNULL((SELECT DISTINCT

SUM
(ISNULL
(CASE
WHEN dbo.inventory_activity_transfer_d.transfer_type = 'F'
AND dbo.inventory_activity_transfer_d.move_from_to='W'
-- AND dbo.inventory_activity_transfer_d.inv_rcpt_trx_id IS NULL
AND dbo.inventory_activity_d.item_id = dbo.items.item_id
THEN (-1)*isnull(dbo.inventory_activity_transfer_d.stocking_quantity,0)
WHEN dbo.inventory_activity_transfer_d.transfer_type = 'T'
AND dbo.inventory_activity_transfer_d.move_from_to='W'
-- AND dbo.inventory_activity_transfer_d.inv_rcpt_trx_id IS NULL
AND dbo.inventory_activity_d.item_id = dbo.items.item_id
THEN (1)*isnull(dbo.inventory_activity_transfer_d.stocking_quantity,0)
ELSE 0
END,0)
)

FROM
dbo.items
JOIN dbo.inventory_activity_d ON dbo.items.item_id = dbo.inventory_activity_d.item_id
JOIN dbo.inventory_activity_transfer_d ON dbo.inventory_activity_d.transaction_no = dbo.inventory_activity_transfer_d.transaction_no
AND dbo.inventory_activity_d.line_no = dbo.inventory_activity_transfer_d.line_no
WHERE
dbo.items.primary_item_category_no<>'NONIN'
AND dbo.items.item_id=t.item_id

),0) AS TTQTY,

ISNULL((SELECT DISTINCT sum(ISNULL(dbo.po_sub_item_d.stocking_quantity,0))
FROM dbo.po_sub_item_d
WHERE dbo.po_sub_item_d.order_completion_date IS NULL
AND dbo.po_sub_item_d.item_id = t.item_id),0) AS OpenPO

FROM
dbo.items t
LEFT JOIN dbo.inventory_receipts_d ON t.item_id = dbo.inventory_receipts_d.item_id
LEFT JOIN dbo.po_sub_item_d ON t.item_id = dbo.po_sub_item_d.item_id
LEFT JOIN dbo.po_sub_change_item_d ON t.item_id = dbo.po_sub_change_item_d.item_id
AND dbo.po_sub_change_item_d.po_sub_no = dbo.po_sub_item_d.po_sub_no
LEFT JOIN dbo.inventory_activity_d ON t.item_id = dbo.inventory_activity_d.item_id
LEFT JOIN dbo.inventory_activity_transfer_d ON dbo.inventory_activity_d.transaction_no = dbo.inventory_activity_transfer_d.transaction_no
AND dbo.inventory_activity_d.line_no = dbo.inventory_activity_transfer_d.line_no



WHERE
t.record_status<>'D'
AND t.primary_item_category_no<>'NONIN'

GROUP BY
t.item_id,t.reorder_point,t.max_stocking_qty,dbo.inventory_receipts_d.item_id, dbo.inventory_activity_transfer_d.transaction_no, dbo.inventory_activity_transfer_d.line_no,dbo.inventory_activity_transfer_d.transfer_type,dbo.inventory_activity_transfer_d.stocking_quantity,dbo.inventory_receipts_d.posted_flag,/*dbo.po_sub_item_d.order_completion_date,*/dbo.inventory_activity_transfer_d.move_from_to,dbo.po_sub_item_d.po_sub_no,dbo.po_sub_item_d.order_completion_date,dbo.inventory_receipts_d.item_id,dbo.inventory_activity_d.transaction_no,dbo.inventory_activity_d.line_no,dbo.inventory_activity_d.item_id,dbo.inventory_activity_transfer_d.inv_rcpt_trx_id

--ORDER BY
-- t.item_id

)
A -- Alias name
ORDER BY
item_id

---