Search Postgresql Archives

Help with join syntax sought

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.

-- 
***          E-Mail is NOT a SECURE channel          ***
James B. Byrne                mailto:ByrneJB@xxxxxxxxxxxxx
Harte & Lyne Limited          http://www.harte-lyne.ca
9 Brockley Drive              vox: +1 905 561 1241
Hamilton, Ontario             fax: +1 905 561 0757
Canada  L8E 3C3


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux