Hi

I think Eirikur's is right, except the summing CTEs were missed. Try Eirikur's with the CTEs in place.

WITH SumSalesQtyCommit AS

(

SELECT [No]

, [LocationCode]

, Sum([QtyCommit]) AS SumQtyCommitTotal

FROM #SalesLine

GROUP BY [No], [LocationCode]

),

SumQtyOnHand AS

(

SELECT [No]

, [LocationCode]

, SUM([QtyRemain]) AS QtyOnHand

FROM #ItemLedgerEntry

GROUP BY [No], [LocationCode]

)

SELECT ile.[No] AS ItemNo

, ile.[LocationCode] AS LocationCode

, ile.QtyOnHand AS QtyOnHandByLocation

, isnull(sot.SumQtyCommitTotal, 0) AS SumQtyCommitTotal

FROM SumQtyOnHand AS ile

LEFT JOIN SumSalesQtyCommit AS sot

ON sot.[No] = ile.[No] AND sot.LocationCode = ile.LocationCode

WHERE ile.[No] = '0000-6896-31 MISC'

ORDER BY ile.[No];