Is it possible to join to tables on dates but where the join is between a single date in one table joined to a date range of the other?

Basically I have one table with transaction values and currencies for daily dates and a second table that has currency exchange rates for different date ranges

eg

Transaction table fields
Client_id Transaction Value Currency ProcessDate

Currency table fields:
Currency Rate (vs EUR) Startdate Enddate

Basically I want to return the currency rate for the given date from the currency table (depending on which range it falls in)

colmkav wrote:
Try this: start with a cartesian join (ie no join clause at all, yields all
possible combinations) and subset the result in your where clause, eg WHERE
ProcessDate BETWEEN Startdate and Enddate.

D

SELECT
.....
WHERE (T1.TransactionDate BETWEEN T2.ExchangeRate_StartDate AND
T2.ExchangeRate_EndDate)
.....

should do the trick.

HTH

Jan T


On 10-Jul-12 11:23, colmkav wrote:

Thanks for your answers. I think a join on the condition:

T1.TransactionDate >= T2.ExchangeRate_StartDate AND T1.TransactionDate <= T2.ExchangeRate_EndDate

also works. Was easier than I thought. Thx!