Using Aggregate functions in joins

SQL Apprentice Question
SELECT
amount,
currencycode
FROM
Expenses (NOLOCK)


We have to select the latest exchnage rate from other table
EXCHANGE_RATE.. this table will contain exchange rate of every date. We
have to take the exchage rate for the latest day and use for
calculating amount in the expenses table.
i tried this but this is not working.


SELECT EX.usdamount *ER.Exchangerate
from EXPENSES EX INNER JOIN EXCHANGE_RATE ER
on EX.currencycode = ER.Currenycode
and ER.Date = max(Date) from Exchange_rate where currencycode =
ER.CurrencyCode


Can anyone help me with this


Celko Answers
>> We have to select the latest exchnage rate from other table EXCHANGE_RATE. this table will contain exchange rate of every date. <<


I would suggest that you create a table more like this:

CREATE TABLE ExchangeRates
(currency_code CHAR(3) NOT NULL,
rate DECIMAL(8,4) NOT NULL,
start_date DATETIME NOT NULL,
end_date DATETIME, -- current rate
CHECK (start_date < end_date),
PRIMARY KEY (currency_code, start_date));


Now use a VIEW to get the current rate. and BETWEEN predicates to match
a rate to a past date. The table will be faster and more useful than
doing a lot of subqueries and aggregates in your code.