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

Join Question

Sort of an esoteric question:

In a transaction with several joins that depend on the prior join statement, does the subsequence join attach to the previous table in its entirety or just the result of it's join?

Example:

INNER JOIN FS_COHeader HDR
ON DTL.COHeaderKey = HDR.COHeaderKey

INNER JOIN Mfg_SHIPDTL SHPDT
ON SHPDT.OMON = HDR.CONUMBER
AND SHPDT._DATESHIP_OwnRec = 11

LEFT OUTER JOIN Mfg_DFSHIP DFSHP
ON DFSHP._SHPLINE_OwnRow = SHPDT._Row

Now will this statement:

LEFT OUTER JOIN Mfg_DFSHIP DFSHP
ON DFSHP._SHPLINE_OwnRow = SHPDT._Row

JOIN with the results of this one:

INNER JOIN Mfg_SHIPDTL SHPDT
ON SHPDT.OMON = HDR.CONUMBER
AND SHPDT._DATESHIP_OwnRec = 11

Or will it JOIN to the entire (previous) table?Use Query Analyser's 'Display Estemated Execution Plan' (ctrl-l) and look for the joins doing table scans for your exact problem. I'd say it should use the subset from the previous join.