Andy Colson wrote:
James B. Byrne wrote:
I have a requirement to select the effective exchange rate for a
number of currencies as of a specific date and time. The rates may
come from several sources for the same currency. For some
currencies the rate may be set infrequently. I have come close to
getting this to work but cannot seem to get the last bit figured
out. Thus my appeal for help.
Here is what I have so far:
SELECT
fxr.currency_code_base AS fx_base,
fxr.currency_code_quote AS fx_quote,
fxr.effective_from AS fx_date,
fxr.currency_exchange_type AS fx_type,
fxr.currency_exchange_rate AS fx_rate
FROM
currency_exchange_rates AS fxr
LEFT OUTER JOIN
currency_exchange_rates AS fxr_j
ON
fxr.currency_code_base = fxr_j.currency_code_base
AND
fxr.currency_code_quote = fxr_j.currency_code_quote
AND
fxr.currency_exchange_type = fxr_j.currency_exchange_type
AND
fxr.effective_from >= fxr_j.effective_from
WHERE
fxr.currency_code_base = 'CAD'
AND
fxr.effective_from <= current_timestamp
GROUP BY
fx_base,
fxr.currency_code_quote,
fx_date,
fxr.currency_exchange_type,
fx_rate
HAVING
COUNT(fxr.currency_code_quote) = 1
ORDER BY
fx_base,
fxr.currency_code_quote,
fx_date DESC
I see currency_code_base = 'CAD', so you are looking for the most recent
Canadian exchange rate.
> The rates may
> come from several sources for the same currency.
What field is the source? currency_code_quote?
-Andy
Hum... I can quite figure what we are looking for.
you say: currencies as of a specific date and time
but there is not date stuff in the where... so we'll ignore that.
you say: come from several sources
but doesnt seem important, we'll ignore that.
you say: I want only the most recent regardless of type
so type is unimporttant
How about this:
select * from fxr where code_base = 'CAD' and effective_from = (select
max(effective_from) from fxr f2 where f2.code_base=fxr.code_base and
f2.code_quote=fxr.code_quote);
(forgive the shortened names), it selects any 'CAD' of only the most
recent time, based on code_base and code_quote. (had to guess at the
fields)
Oh! I see one problem.. if the effective_from is exactly the same it
could return more records than you want. Have to think about that...
-Andy
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
