Hi Josh,

Try the following (I've assumed the combo of InventoryItemID and
EffectiveDate are unique):

SELECT *
FROM tblInventoryItem i
, tblPricing p
,(SELECT InventoryItemID
, MAX(EffectiveDate) as PriceDate
FROM tblPricing
WHERE EffectiveDate <= @YourDate
GROUP BY InventoryItemID
) cp
WHERE i.InventoryItemID = cp.InventoryItemID
AND cp.InventoryItemID = p.InventoryItemID
AND p.Effective_date = cp.PriceDate;

You can achieve the same result using a correlated sub-query, but this
method works best for me across different DBs.

Christian.