Determine maximum based on effective date

I have 2 tables, item and item cost.  They join on an item_urn field.   Item is the master table of inventory.  Item cost us a list of all of the past and current costs of the items so there is a 1 to many relationship of items to item_cost.  The item cost table has a item_urn, an effective date and an amount.  What I need to do is to get the amount associated with the latest effective date for each item_urn.  This seems very simple but I can't seem to get it.

Who is Participating?
SwindleConnect With a Mentor Commented:
If you are allowed to use a subquery, you should be able to do something similar to this:

SELECT a.item_urn, b.amount
FROM item a
   LEFT JOIN item_cost b ON a.item_urn = b.item_urn
WHERE b.amount IN (
   SELECT  top 1 amount
   FROM item_cost
   WHERE item_urn = a.item_urn
   ORDER BY effectiveDate desc
dqmqConnect With a Mentor Commented:
Select i1.*, c1.* from item_cost c1
inner join item i1 on i1.item_urn = c1.item_urn
where c1.effective_date =
   (select max(c2.effective_date)
    from item_cost c2 where c1.item_urn = c2.item_urn)

k_rasuriConnect With a Mentor Commented:
SELECT item, ItemCost, MAX(Date) AS LatestDate FROM ItemTable
INNER JOIN ItemCostTable ON ItemTable.item_urn=ItemCostTable.item_urn
GROUP BY Item, ItemCost
BudGarrisonAuthor Commented:
Thanks guys, the first 2 solutions worked great.  The third also did the trick but with multiple rows per item.
Be advised, these solutions produce different results:

first:  Returns the row associated with the greatest amount, not the latest effective date. Only works when prices are going up!

second: Returns the row associated with the geatedst effective date (as requested)

Third: Returns multiple rows per item, each containing a different item cost with the max effective date of all item costs.  Nonsense.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.