I have two tables Expense_Addl and ExchangeRates

These are the scripts


CREATE TABLE Expense_Addl (
        FileId int,
        HostAmt money,
        HostCurrencyCd char(4),
        BaseCurrencyCd Char(4),
        LockDt smalldatetime
        )


INSERT INTO Expense_addl values (1001, 500, 'INR','USD', '2006-06-15')
INSERT INTO Expense_addl values (1001, 500, 'INR','USD', '2006-06-19')
INSERT INTO Expense_addl values (1001, 500, 'INR','USD', '2002-01-01')

--In the above table HostCurrencycd, BaseCurrencyCd can be any
currenycode like 'USD',
--'INR', 'YEN'


CREATE TABLE ExchangeRates
(CurrencyCd CHAR(4), ExchangeRate MONEY, ExchangeRateDate
SMALLDATETIME)


INSERT INTO ExchangeRates values ('INR', 0.45, '2006-06-21')
INSERT INTO ExchangeRates values ('INR', 0.40, '2006-06-14')
INSERT INTO ExchangeRates values ('USD', 0.22, '2006-06-22')

Now i have to update the HostAmt in Expense_Addl table taking
exchangerate for the hostcurrencycd, BaseCurrencyCd from table
ExchangeRates
for the nearest date to LockDt like this


For e.g. HOstAmt in first row should take exchangerate as 0.40 for
'INR' because
lockdt 15th june is closet to 14th june
Similarly HOstAmt in second row should take exchangerate as 0.45 for
'INR'
because lockdt 19th june is closet to 21st june
Similarly HOstAmt in third row should take exchangerate as 0.40 for
'INR' because
lockdt 01st jan is closet to 14th june

Similarly HostAmt should take nearest exchangerate for BaseCurrencyCd
from ExchangeRates table



I am trying something like below but not getting the correct results.


UPDATE EA
SET HostAmt = ROUND(CONVERT(MONEY,(ISNULL(((CONVERT(FLOAT, EA.HostAmt))
* ISNULL(ER1.ExchangeRate,1)) /
NULLIF(ISNULL(ER2.ExchangeRate,0),0),0))), 4)
FROM Expense_Addl EA(NOLOCK)
INNER JOIN ExchangeRates ER1(NOLOCK) ON EA.BaseCurrencyCd =
ER1.CurrencyCd
INNER JOIN
(SELECT CurrencyCd, MAX(ExchangeRateDate)  As ExchangeRateDate
             FROM ExchangeRates ER ,Expense_Addl EA(NOLOCK) Where
ER.ExchangeRateDate <= EA.lockdt
             GROUP BY ER.CurrencyCd
             ) T1
  ON T1.ExchangeRateDate = ER1.ExchangeRateDate AND T1.CurrencyCd =
ER2.CurrencyCd
INNER JOIN ExchangeRates ER2(NOLOCK) ON EA.HostCurrencyCd =
ER2.CurrencyCd
INNER JOIN
(SELECT CurrencyCd, MAX(ExchangeRateDate)  As ExchangeRateDate
           FROM ExchangeRates ER ,Expense_Addl EA(NOLOCK) Where
ER.ExchangeRateDate <= EA.lockdt
             GROUP BY ER.CurrencyCd
             ) T2
  ON T2.ExchangeRateDate = ER2.ExchangeRateDate AND T2.CurrencyCd =
ER2.CurrencyCd

Can anyone help me with this ?

Thanks and Regards,
Rajeev Rajput

Just what results DO you get back, and what about them is not correct?

I tried it and all I got was:

Server: Msg 107, Level 16, State 2, Line 1
The column prefix 'ER2' does not match with a table name or alias name
used in the query.

This appears to result from referenceing ER2 in the ON clause of the
second join, when ER2 is not intruduced until the next JOIN after
that.

Roy Harvey
Beacon Falls, CT

Subquery performance concern
A query runs 1 times slower from a .NET application the from Query Analizer
Help with varchar to date
schedule dts at a remote server
can a SELECT return this result ?!