Getting unique records daily currency rates

User_SQSR4

I have table like this

with t as (

select 'USD' FROM_CURRENCY ,

'INR' TO_CURRENCY ,

to_date('01-JAN-2021', 'DD-MON-YYYY') FROM_CONVERSION_DATE,

to_date('01-JAN-2021', 'DD-MON-YYYY') TO_CONVERSION_DATE,

81.5 CONVERSION_RATE

from dual

union all 

select 'INR' FROM_CURRENCY ,

'USD' TO_CURRENCY ,

to_date('01-JAN-2021', 'DD-MON-YYYY') FROM_CONVERSION_DATE,

to_date('01-JAN-2021', 'DD-MON-YYYY') TO_CONVERSION_DATE,

0.0122 CONVERSION_RATE

from dual

union all

select 'SGD' FROM_CURRENCY ,

'INR' TO_CURRENCY ,

to_date('01-JAN-2021', 'DD-MON-YYYY') FROM_CONVERSION_DATE,

to_date('01-JAN-2021', 'DD-MON-YYYY') TO_CONVERSION_DATE,

48 CONVERSION_RATE

from dual

union all

select 'USD' FROM_CURRENCY ,

'INR' TO_CURRENCY ,

to_date('02-JAN-2021', 'DD-MON-YYYY') FROM_CONVERSION_DATE,

to_date('02-JAN-2021', 'DD-MON-YYYY') TO_CONVERSION_DATE,

82.5 CONVERSION_RATE

from dual

union all 

select 'INR' FROM_CURRENCY ,

'USD' TO_CURRENCY ,

to_date('02-JAN-2021', 'DD-MON-YYYY') FROM_CONVERSION_DATE,

to_date('02-JAN-2021', 'DD-MON-YYYY') TO_CONVERSION_DATE,

0.0121 CONVERSION_RATE

from dual)

select * from t

;

I would like to suppress the inverse conversion rate of a currency and need the unique records.

From the above 5 reocrds , expected Output is only three reocords.

FROM CURENCY TO_CURRENCY FROM_DATE TO_DATE CONVERSION_RATE

USD INR 01-Jan-2021 01-Jan-2021 81.5

SGD INR 01-Jan-2021 01-Jan-2021 48

USD INR 02-Jan-2021 02-Jan-2021 82.5

RogerT

The question is ... which one is the inverse conversion? But you could something like this (i did it in multiple steps to show how i got there ... you can combine those steps of course).

with t as (select 'USD' FROM_CURRENCY ,'INR' TO_CURRENCY ,date '2021-01-01' FROM_CONVERSION_DATE, date '2021-01-01' TO_CONVERSION_DATE,81.5 CONVERSION_RATE from dual union all 
      select 'INR' FROM_CURRENCY ,'USD' TO_CURRENCY ,date '2021-01-01' FROM_CONVERSION_DATE,date '2021-01-01' TO_CONVERSION_DATE,0.0122 CONVERSION_RATE from dual union all
      select 'SGD' FROM_CURRENCY ,'INR' TO_CURRENCY ,date '2021-01-01' FROM_CONVERSION_DATE,date '2021-01-01' TO_CONVERSION_DATE,48 CONVERSION_RATE from dual union all
      select 'USD' FROM_CURRENCY ,'INR' TO_CURRENCY ,date '2021-01-02' FROM_CONVERSION_DATE,date '2021-01-02' TO_CONVERSION_DATE,82.5 CONVERSION_RATE from dual union all 
      select 'INR' FROM_CURRENCY ,'USD' TO_CURRENCY ,date '2021-01-02' FROM_CONVERSION_DATE,date '2021-01-02' TO_CONVERSION_DATE,0.0121 CONVERSION_RATE from dual)
  ,PREPARED AS (SELECT LEAST(FROM_CURRENCY,TO_CURRENCY) || '#' || GREATEST(FROM_CURRENCY,TO_CURRENCY) AS UNIQUE_CONVERSION, FROM_CURRENCY, TO_CURRENCY, FROM_CONVERSION_DATE, TO_CONVERSION_DATE, CONVERSION_RATE, 1/CONVERSION_RATE AS REVERSE_CONVERSION_RATE 
          FROM T)
  ,RANKED AS (SELECT UNIQUE_CONVERSION
           ,FROM_CURRENCY
           ,TO_CURRENCY
           ,FROM_CONVERSION_DATE
           ,TO_CONVERSION_DATE
           ,CONVERSION_RATE
           ,row_number() OVER (PARTITION BY UNIQUE_CONVERSION, FROM_CONVERSION_DATE, TO_CONVERSION_DATE ORDER BY CONVERSION_RATE DESC) AS RANKING
         FROM PREPARED)
select from_currency, to_currency, from_conversion_date, to_conversion_date, conversion_rate
 from ranked
 where ranking = 1;

mathguy

When both directions of a conversion rate are present, you didn't tell us which you want to keep. If "either one is good, I just need one for each pair of currencies" works for you, then you could do something like I show below. If you have a more specific rule for which side to choose when both are present, that can be arranged as well.

The query below does everything in a single aggregate query (it does not use analytic functions). As you see, I considered myself free to choose any one of a pair of conversion rates; for the USD/INR pairs, this query shows the conversion from INR to USD. As I said, if you need a more specific choice when both pairs are present, you should say so (if you already did in your post, I missed it).

with
  t (from_currency, to_currency, from_conversion_date, to_conversion_date, conversion_rate) as ( 
    select 'USD', 'INR', date '2021-01-01', date '2021-01-01', 81.5    from dual union all 
    select 'INR', 'USD', date '2021-01-01', date '2021-01-01',  0.0122 from dual union all
    select 'SGD', 'INR', date '2021-01-01', date '2021-01-01', 48      from dual union all
    select 'USD', 'INR', date '2021-01-02', date '2021-01-02', 82.5    from dual union all 
    select 'INR', 'USD', date '2021-01-02', date '2021-01-02',  0.0121 from dual
  )
select min(from_currency) as from_currency
     , max(to_currency) as to_currency
     , from_conversion_date
     , to_conversion_date
     , min(conversion_rate) keep (dense_rank first order by from_currency) as conversion_rate
from   t
group  by from_conversion_date, to_conversion_date, 
          least(from_currency, to_currency), greatest(from_currency, to_currency)
;


FROM_CURRENCY  TO_CURRENCY    FROM_CONVERSION_DATE TO_CONVERSION_DATE   CONVERSION_RATE
-------------- -------------- -------------------- -------------------- ---------------
SGD            INR            2021-01-01           2021-01-01                        48
INR            USD            2021-01-01           2021-01-01                     .0122
INR            USD            2021-01-02           2021-01-02                     .0121