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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
