join 2 tables on date to a date range - 07-10-2012 , 04:23 AM
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
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)
Re: join 2 tables on date to a date range - 07-10-2012 , 06:49 AM
possible combinations) and subset the result in your where clause, eg WHERE
ProcessDate BETWEEN Startdate and Enddate.
Re: join 2 tables on date to a date range - 07-10-2012 , 06:53 AM
WHERE (T1.TransactionDate BETWEEN T2.ExchangeRate_StartDate AND
should do the trick.
On 10-Jul-12 11:23, colmkav wrote:
Re: join 2 tables on date to a date range - 07-10-2012 , 07:48 AM
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!