# 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.

Friday, November 22, 2013 12:52 AM

• 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 txHowever, 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