How can I create a query that does a range lookup? 1

[lleach9]

I have two tables, one that contains an item key, an effective date, and a price and a second table that contains an item key and a transaction date. I would like to create a query that would join the tables to show

item key
transaction date
price (where the effective date is less than the transaction date, but excluding earlier effective dates)

Any ideas on how to construct that query? Thanks

 

[foxdev]

If you want an inner join (only records that appear in both tables will be included):

[tt]select Table1.ItemKey, EffDate, Price, TransDate
from Table1
join Table2 on Table2.ItemKey = Table1.ItemKey[/tt]
Robert Bradley

http://www.foxdev.com/

http://www.agrainofhope.org

 

[lleach9]

It is a little more complicated than that. In the case of a price table with 4 prices for each item, with a different effective date for each price, your suggestion would return 4 rows for each transaction. I am trying to narrow the result down to one row for each transaction, showing the price effective on the date of the transaction.

Thanks

 

[jballum]

select
Table1.ItemKey,
Table1.Price,
Table2.TransDate
from
(select Table1A.ItemKey,
Table1A.EffDate,
Table1A.Price
from
Table1A
inner join
(select MAX(Table1B.ItemKey) ItemKey,
MAX(Table1B.EffDate) EffDate
from Table1B
group by datepart(dy, Table1B.EffDate))
Table1C
on Table1A.ItemKey = Table1C.ItemKey and
Table1A.EffDate = Table1C.EffDate)
Table1
inner join
Table2 on Table1.ItemKey = Table2.ItemKey

Messy messy and I didn't have a chance to take too much of a look at it but it might work or at least give you an idea.

JB

 

[lleach9]

Thanks, you got me on the right track. I ended up with the following:

(
select
A1.item_key,
A1.trans_date,
MAX(A1.effective_date) Effective_date
from
(
select
price.item_key,
trans.trans_date,
price.effective_date,
from
price,
trans
where
price.effective_date < trans.trans_date
) A1
group by A1.item_key,A1.trans_date
) A2

This gives me the correct effective date for each transaction. From there I simply join the trans table to the price table by item_key and join the summary above by trans.item_key=price_item_key, trans.item_key=A2.item_key, trans.trans_date=A2.trans_date, price.effective_date=A2.effective_date

Thanks

 

[lleach9]

Thanks, you got me on the right track. I ended up with the following:

(
select
A1.item_key,
A1.trans_date,
MAX(A1.effective_date) Effective_date
from
(
select
price.item_key,
trans.trans_date,
price.effective_date,
from
price,
trans
where
price.effective_date < trans.trans_date
) A1
group by A1.item_key,A1.trans_date
) A2

This gives me the correct effective date for each transaction. From there I simply join the trans table to the price table by item_key and join the summary above by trans.item_key=price_item_key, trans.item_key=A2.item_key, trans.trans_date=A2.trans_date, price.effective_date=A2.effective_date

Thanks