CODEWITHSUNDEEP
oracle-database
Abdusoli
Abdusoli

Reputation: 659

How to Convert From one currency into another in Oracle query

I am selecting filial expenditures in US dollar for a particular date. But I need to convert it into Won currency for that date. Currencies`s value changes at any time according to US dollar. Currency history is in table of V_CURRENCY. I need to multiply dollar to that amount and show in Won. My query is below and giving an error. Error is [22008][1861] ORA-01861. Mismatch of rows.

with cte as (
    select t.filial_code,
           t.modified_by                      as emp_code,
           sum(t.sum_oper)                    as summa,
           to_char(t.oper_date, 'YYYY-MM-DD') as operation_date
    from OPERS t,
         DEP_OPERS d
    WHERE 
      And t.modified_by = 213
      And t.filial_code = '00116'
      And d.currency_code = 840
      And t.oper_date >= to_date('07.01.2020', 'DD.MM.YYYY')
      And t.oper_date < to_date('11.01.2020', 'DD.MM.YYYY')
    group by to_char(t.oper_date, 'YYYY-MM-DD'), t.filial_code, t.modified_by
) select cte.filial_code,cte.emp_code,cte.summa * (select equival from V_CURRENCY
    where date_activ = (select max(date_activ)
                            from V_CURRENCY
                         where date_activ <= cte.operation_date) and code = 840) as summa,cte.operation_date from cte;

From cte I am taking below result :

FILIAL_CODE | EMP_CODE | SUMMA | OPERATION_DATE
-----------------------------------------------
00116       | 213      | 40000 | 2020-01-14
00116       | 213      | 6000  | 2020-01-10
00116       | 213      | 2800  | 2020-01-06

My V_CURRENCY table is like below:

CODE   | DATE_ACTIV             | EQUIVAL|
--------------------------------
840    | 2020-01-13 00:00:00    | 576.97
840    | 2020-01-07 00:00:00    | 2008.54
840    | 2020-01-06 00:00:00    | 1941.91
840    | 2019-12-22 00:00:00    | 301.62
190    | 2020-01-13 00:00:00    | 1200.97
270    | 2020-01-13 00:00:00    | 2300.21
800    | 2019-12-22 00:00:00    | 100.62

I need to multiply equival from table V_CURRENCY in date 2020-01-13 00:00:00 to my cte result SUMMA for OPERATION_DATE 2020-01-14, means that 4000 * 576.97, cause OPERATION_DATE is closest one to currency change date. But when DATE_ACTIV exists for date 2020-01-06 then 2800 * 1941.91. I only need curreny value whose code is 840.

My last result should look like :

FILIAL_CODE | EMP_CODE | SUMMA           | OPERATION_DATE
-----------------------------------------------
00116       | 213      | 40000 * 576.97  | 2020-01-14
00116       | 213      | 6000 *  2008.54 | 2020-01-10
00116       | 213      | 2800  * 1941.91 | 2020-01-06

Any response appreciated. Thanks in advance.

Upvotes: 0

Views: 956

Answers (1)

MT0
MT0

Reputation: 167962

Use the LEAD analytic function to find the date the currency conversion is valid up until and then join the tables on the range between the start and end dates the currency is valid for:

SELECT y.filial_code,
       y.emp_code,
       y.summa * c.equival AS summa,
       y.operation_date
FROM   your_cte y
       INNER JOIN (
         SELECT c.*,
                LEAD( DATE_ACTIV, 1, SYSDATE )
                  OVER ( PARTITION BY code ORDER BY date_activ )
                  AS DATE_FINISHED
         FROM   v_currency c
         WHERE  currency_code = 840
       ) c
       ON (   y.operation_date >= c.date_activ
          AND y.operation_date <  c.date_finished )

Outputs:

FILIAL_CODE | EMP_CODE |    SUMMA | OPERATION_DATE
:---------- | -------: | -------: | :-------------
00116       |      213 | 23078800 | 14-JAN-20     
00116       |      213 | 12051240 | 10-JAN-20     
00116       |      213 |  5437348 | 06-JAN-20

db<>fiddle here

As an aside, your CTE doesn't need to convert dates to strings and can use date literals and a modern ANSI join (rather than legacy comma join):

with cte ( filial_code, emp_code, summa, operation_date ) as (
    SELECT t.filial_code,
           t.modified_by,
           sum(t.sum_oper),
           t.oper_date
    from   OPERS t
           CROSS JOIN
           DEP_OPERS d
    WHERE  t.modified_by   = 213
    AND    t.filial_code   = '00116'
    AND    d.currency_code = 840
    AND    t.oper_date     >= DATE '2020-01-07'
    AND    t.oper_date     <  DATE '2020-01-11'
    GROUP BY t.oper_date,
             t.filial_code,
             t.modified_by
)
...

and you don't even need the CTE:

SELECT t.filial_code,
       t.modified_by               AS emp_code,
       SUM(t.sum_oper * c.equival) AS summa,
       t.oper_date                 AS operation_date
FROM   OPERS t
       CROSS JOIN
       DEP_OPERS d
       INNER JOIN (
         SELECT c.*,
                LEAD( DATE_ACTIV, 1, SYSDATE )
                  OVER ( PARTITION BY code ORDER BY date_activ )
                  AS DATE_FINISHED
         FROM   v_currency c
       ) c
       ON (   t.oper_date     >= c.date_activ
          AND t.oper_date     <  c.date_finished
          AND d.currency_code = c.currency_code )
WHERE  t.modified_by   = 213
AND    t.filial_code   = '00116'
AND    d.currency_code = 840
AND    t.oper_date     >= DATE '2020-01-07'
AND    t.oper_date     <  DATE '2020-01-11'
GROUP BY t.oper_date,
         t.filial_code,
         t.modified_by

Upvotes: 2

Related Questions