Link date to a table with Date Range (Eff Date and term date)

[Guest]

I have a price table (A) with multiple reocords and a code to identify each
price and an effective date and termination date.

I also have a much larger table (B) with 2.2 mil records with transaction
and the date they where used. Since the prices change on average quterly and
the file is for 3 years, is there a way to make to make table (B) look at
the code being used and determine rather the date is within the range.

 

[Pieter Wijnen]

Something like

SELECT A.*,B.PRICE FROM TRANS A INNER JOIN PRICES B ON A.ITEMID = B.ITEMID
WHERE A.DATE BETWEEN B.STARTDATE AND B.ENDDATE

might be what you're looking for

Pieter