SQLize Online / PHPize Online  /  SQLtest Online

with transaction as ( select 1 id, to_date('23-05-2022', 'dd-mm-yyyy') transaction_date, 33 client_id, 'EURO' currency, 259 amount from dual union all select 2 id, to_date('24-05-2022', 'dd-mm-yyyy') transaction_date, 34 client_id, 'USD' currency, 300 amount from dual union all select 3 id, to_date('25-05-2022', 'dd-mm-yyyy') transaction_date, 34 client_id, 'RUR' currency, 5000 amount from dual union all select 4 id, to_date('25-05-2022', 'dd-mm-yyyy') transaction_date, 34 client_id, 'EURO' currency, 50 amount from dual union all select 4 id, to_date('26-05-2022', 'dd-mm-yyyy') transaction_date, 34 client_id, 'EURO' currency, 50 amount from dual union all select 4 id, to_date('27-05-2022', 'dd-mm-yyyy') transaction_date, 34 client_id, 'EURO' currency, 50 amount from dual ), currency as ( select 1 id, to_date('23-05-2022', 'dd-mm-yyyy') currency_date, 'EURO' currency, 69.0 VALUE from dual union all select 2 id, to_date('23-05-2022', 'dd-mm-yyyy') currency_date, 'USD' currency, 60 VALUE from dual union all select 3 id, to_date('24-05-2022', 'dd-mm-yyyy') currency_date, 'EURO' currency, 70 VALUE from dual union all select 4 id, to_date('24-05-2022', 'dd-mm-yyyy') currency_date, 'USD' currency, 59.5 VALUE from dual union all select 5 id, to_date('27-05-2022', 'dd-mm-yyyy') currency_date, 'EURO' currency, 71 VALUE from dual) , dates as ( select distinct transaction_date, currency from transaction) select t.clietn_id, decode(d.currency, 'RUR', 1, coalesce(c.value, (select fc.value from currency fc where fc.currency = d.currency and fc.currency_date <= d.transaction_date order by fc.currency_date DESC fetch first 1 row only))) * t.amount as amountrub from dates d join transaction t on d.transaction_date = t.transaction_date and d.currency = t.currency left join currency c on d.transaction_date = c.currency_date and d.currency = c.currency