none
Comparing two rows

    Question

  • I am trying to get a price for an item for historical prices. I have the following tables:

    1.  TxnTable-

    a. ItemIdentifier

    b. ItemCode

    c. SellDate

    d. Purchaser

    2.  PriceHistory

    a. PriceIdentifier

    b. ItemCode

    c. Price

    d. PriceChangeDate

    I need to look at some items in the TxnTable and get the price for the items, to determine the total sales during a year. Here is what I need to do.

    A. Take the ItemCode and SellDate from the TxnTable

    B. Compare that to the PriceChangeDate and ItemCode in the PriceHistoryTable to obtain the price of the item at that time.

    This needs to be done for hundreds of the same ItemCodes as well as to loop it through for multiple items.

    And before you ask- the InvoiceTable is not present or has been Corrupted so I cannot look for it there.

    Thanks in advance.

    Friday, November 22, 2013 12:52 AM

Answers

  • Refer "AND PriceChangeDate < SellDate"

    This will not return the exact price from PriceHistory. In order to get the price of an item we need to search for exact match. So use CONVERT function(truncating time) as i have used in my example. There can be multiple and similar PriceChangeDate in the history table, so we can pick the recent top 1 price that exactly match the priceChangeDate and SellDate of an Item.

    Also add TRIM in tx.ItemCode = ph.ItemCode, i.e

    RTRIM(LTRIM(tx.ItemCode)) = RTRIM(LTRIM(ph.ItemCode))


    Regards, RSingh

    Saturday, November 23, 2013 2:53 AM

All replies

  • Try this,

    drop table #TxnTable
    drop table #PriceHistory
    create table #TxnTable(ItemIdentifier int, ItemCode varchar(10), SellDate datetime, Purchaser int)
    insert into #TxnTable values(1,'Item1', getdate(), 1)
    insert into #TxnTable values(2,'Item2', getdate(), 1)
    insert into #TxnTable values(3,'Item3', getdate(), 1)
    create table #PriceHistory(PriceIdentifier int, ItemCode varchar(10), Price money, PriceChangeDate datetime)
    insert into #PriceHistory values(1,'Item1', 200, getdate() )
    insert into #PriceHistory values(2,'Item2', 300, getdate() )
    insert into #PriceHistory values(3,'Item3', 500, getdate() )
    select *,
    	(select top 1 Price from #PriceHistory b 
    	where b.ItemCode=a.ItemCode and 
    	convert(char(10),a.SellDate,101) = convert(char(10),b.PriceChangeDate,101)
    	) Price
    from #TxnTable a
    --------OR
    select * from #TxnTable a left outer join #PriceHistory b on 
    a.ItemCode=b.ItemCode and 
    convert(char(10),a.SellDate,101) = convert(char(10),b.PriceChangeDate,101)


    Regards, RSingh

    Friday, November 22, 2013 4:12 AM
  • RSingh,

    Thanks for the ideas.  These tables cannot be deleted and recreated.  This information is coming from multiple sources into one database.  I have tried something similar:

    SELECT
      tx.ItemIdentifier,
      tx.ItemCode,
      tx.SellDate,
      tx.Purchaser,
      ( SELECT TOP 1 Price
        FROM PriceHistory ph
        WHERE tx.ItemCode = ph.ItemCode
          AND PriceChangeDate < SellDate
        ORDER BY PriceChangeDate DESC
      ) Price 
    FROM TxnTable tx

    However, it is not pulling the correct prices or even prices related to the correct Item code. Maybe there is something in the code that I am missing.

    Thanks,

    Scott

    Friday, November 22, 2013 5:57 PM
  • Refer "AND PriceChangeDate < SellDate"

    This will not return the exact price from PriceHistory. In order to get the price of an item we need to search for exact match. So use CONVERT function(truncating time) as i have used in my example. There can be multiple and similar PriceChangeDate in the history table, so we can pick the recent top 1 price that exactly match the priceChangeDate and SellDate of an Item.

    Also add TRIM in tx.ItemCode = ph.ItemCode, i.e

    RTRIM(LTRIM(tx.ItemCode)) = RTRIM(LTRIM(ph.ItemCode))


    Regards, RSingh

    Saturday, November 23, 2013 2:53 AM