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 d.transaction_date, d.currency, c.value, 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)) as value from dates d left join currency c on d.transaction_date = c.currency_date and d.currency = c.currency order by transaction_date, currency