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.
The table currency_exchange_rates has a composite unique index made
up of:
fxr.currency_code_base
fxr.currency_code_quote
fxr.effective_from
fxr.currency_exchange_type
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
My problem with this version is that currencies with rates from more
than one type show up for each type. This I do not want. I want
only the most recent regardless of type. However, I need to display
the type in the final report.
Further, if I take the fxr.currency_exchange_type out of the SELECT
columns, making the appropriate adjustments elsewhere, then all
those currencies with more than one type are not selected at all.
I am perplexed why I cannot select a column from the table without
having to include it in the GROUP BY clause as well.
Any help is welcomed.
If your query above is getting you mostly what you want, just use it as
a derived table.
so:
select subtable.fx_base, etc from (
> 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
) as subtable
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
