Re: Help with join syntax sought

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