Determine maximum based on effective date

Posted on 2007-08-07
Last Modified: 2013-11-05
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.

Question by:BudGarrison
    LVL 11

    Accepted Solution

    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
    LVL 42

    Assisted Solution

    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)

    LVL 8

    Assisted Solution

    SELECT item, ItemCost, MAX(Date) AS LatestDate FROM ItemTable
    INNER JOIN ItemCostTable ON ItemTable.item_urn=ItemCostTable.item_urn
    GROUP BY Item, ItemCost

    Author Comment

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

    Expert Comment

    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

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now