asked on

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.

Thanks,
Bud

ASKER CERTIFIED SOLUTION

Swindle

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

dqmq

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

k_rasuri

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

BudGarrison

ASKER

Thanks guys, the first 2 solutions worked great. The third also did the trick but with multiple rows per item.

dqmq

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.