Even though you said that you attached desired results, I'm not seeing them. Would the following work?

;

WITH LotHistOrdered AS (

SELECT *, ROW_NUMBER()OVER(PARTITION BY lh.LotNo ORDER BY lh.TransType DESC, lh.TransDate DESC, lh.TransNo DESC) AS rn

FROM @LotHist lh

)

SELECT LOT.LotNo, ITEM.ItemNo, ITEM.[DESC], LOT.Warehouse, LOT.CurrentQty, LOT.Cost, LOTHIST.TransDate, lothist.TransType, LOTHIST.TransQty

FROM @LOT lot

INNER JOIN @ITEM item

ON LOT.ItemNo = ITEM.ItemNo

INNER JOIN LotHistOrdered lothist

ON LOTHIST.LotNo = LOT.LotNo

WHERE lothist.rn = 1

AND (LOTHIST.TransType = 'Shipment'

ORLOTHIST.TransType = 'Receipt' AND LOT.CurrentQty > 0 AND (LOTHIST.TransDate) > GETDATE()- 90

)

It uses a CTE with a ROW_NUMBER to sort the records in order of relevance and then selects only the most relevant row. (I'm not sure that I got the correct order of relevance.)

Drew

PS: I prefer to use temp tables or table variables for sample data, because I don't want to clutter up my dev environment if I forget to DROP them.