by lucie.sperkova@gmail.com

Description: We’ve realized that ECB is not providing exchange rates during weekends and holidays.

Here you can find UPDATE statement which fills the the gaps with the nearest previous NOT NULL value.

Tables:

• <dates> - list of dates

• <rates> - exchange rates

/* joins the rates table to dates table so we recognize what dates dont have rates*/

CREATE TABLE temp AS

SELECT

dates.date as datum,

rates.*

FROM dates

LEFT JOIN rates

ON rates.date=dates.date

WHERE dates.date BETWEEN '2010-01-04' AND CURRENT_DATE

ORDER BY 1;

/*creates a column with the previous date - the values are relevant only for the null records*/

CREATE TABLE prev_date as

select datum, fromcurrency, tocurrency, rate,

lag(date,1) ignore nulls over (order by datum) as prev_date

from temp;

/* joins not null records from table temp with the filled gaps*/

CREATE TABLE currency AS

(SELECT

datum as date, fromcurrency, tocurrency, rate from temp where rate is not null)

UNION

/* joins previous date to current date and fills the nulls with the records from the previous date*/

(select prev_date.datum as date, pv.fromcurrency, pv.tocurrency, pv.rate 

from prev_date

left join prev_date pv on prev_date.prev_date = pv.datum and pv.rate is not null

where prev_date.rate is null);