• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 278
  • Last Modified:

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.

3 Solutions
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
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)

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.

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now