Join to a single date rate table?

Given an exchange rate table like:
Create Table XRates
(
EffectiveDate smalldatetime,
FromCury char(3),
ToCury char(3),
Rate float
)
and Transaction table like
Create Table Trans
(
TranDate smalldatetime,
TranCury char(3),
TranAmt float
)
What would be the best SQL query to join from the Transaction table to the
correct exchange rate based on the transaction date? Any sugestions?
Thanks in advanceHere is one way (SQL Server 2005):
WITH Transactions
AS
(SELECT T.TranDate, T.TranCury, T.TranAmt,
R.ToCury, R.Rate,
ROW_NUMBER() OVER(
PARTITION BY R.FromCury
ORDER BY R.EffectiveDate DESC) AS seq
FROM Trans AS T
JOIN XRates AS R
ON T.TranCury = R.FromCury
AND T.TranDate >= R.EffectiveDate)
SELECT TranDate, TranCury, TranAmt, ToCury, Rate
FROM Transactions
WHERE seq = 1;
HTH,
Plamen Ratchev
http://www.SQLStudio.com