SQL for Smarties | SQL Programming Style | Trees and Hierarchies in SQL | SQL Puzzles and Answers | Data and Databases


Wednesday, May 31, 2006

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.

No comments: