I am looking at pricing for inventory items. Some items are returned more than once because they have multiple pricing in the system. I need to get the MAX pricing for each item. The query is below.
a.Item_Number is the item that may be returned multiple times if there is multiple prices
b.unit_cost is what I want to get the MAX for each individual item
[OnHand] = Qty_Received - Qty_Sold,
[CostDiff] = a.Standard_Cost - b.Unit_Cost
FROM dbo.tspvItemMaster a
LEFT JOIN dbo.tspvInventoryReceipts b ON a.Item_Number = b.Item_Number
WHERE Qty_Received - Qty_Sold > 0
AND ( a.Standard_Cost - b.Unit_Cost < -0.02 )
AND a.Item_Number NOT LIKE '*%'
AND Item_Class_Code <> 'used equip'
AND Standard_Cost <> '0.00'
ORDER BY (a.Standard_Cost-b.Unit_Cost)