Getting unique records daily currency rates — oracle-tech

    Forum Stats

  • 3,714,823 Users
  • 2,242,634 Discussions
  • 7,845,082 Comments

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Getting unique records daily currency rates

User_SQSR4
User_SQSR4 Member Posts: 6 Employee

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

Tagged:

Answers

  • RogerT
    RogerT Member Posts: 1,850 Gold Trophy

    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
    mathguy Member Posts: 9,463 Gold Crown

    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
    


Sign In or Register to comment.