SQLize Online / PHPize Online  /  SQLtest Online

create table Rate( id int not null, code varchar(50) not null, date datetime not null, value float not null ); insert into Rate (id, code, date, value) values (1,'EUR','20090605', 1.149); insert into Rate (id, code, date, value) values (2,'EUR','20090615', 1.161); insert into Rate (id, code, date, value) values (3,'EUR','20090617', 1.177); insert into Rate (id, code, date, value) values (4,'USD','20090605', 1.625); insert into Rate (id, code, date, value) values (5,'USD','20090615', 1.639); insert into Rate (id, code, date, value) values (6,'USD','20090617', 1.644); create table Calendar ( code VARCHAR(3) not null, dDay datetime not null, value float not null, weekday VARCHAR(10) not null ); SET @start_dt := TIMESTAMP('20090605'); SET @end_dt := TIMESTAMP('20090619'); SELECT code, date, IFNULL(LEAD(date) OVER (), @max_dt) as nextdt, value FROM Rate WHERE code = "EUR" UNION SELECT code, date, IFNULL(SUBDATE(LEAD(date) OVER (PARTITION BY code ORDER BY date), INTERVAL 1 DAY), @max_dt) as nextdt, value FROM Rate WHERE code = "USD" ORDER BY code, date; SET @row_num := 0; SELECT *, (@max_dt := DATE_ADD(@max_dt, INTERVAL 1 DAY)) AS str_num FROM Rate; SELECT code, TIMESTAMP(dt) AS dDay, value as value, DAYNAME(dt) AS weekday FROM dates d LEFT JOIN Rate r ON d.dt >= MAX(r.date) WHERE DAYNAME(dt) NOT IN ('Saturday', 'Sunday') ORDER BY code, dt; INSERT INTO Calendar(code, dDay, value, weekday) WITH RECURSIVE dates(dt) AS ( SELECT @start_dt UNION ALL SELECT IF(dayofweek(dt) <> 6, DATE_ADD(dt, INTERVAL 1 DAY), DATE_ADD(dt, INTERVAL 3 DAY) ) FROM dates WHERE dt < @end_dt ), intervals(code, stdt, spdt, val) AS ( SELECT code, date, IFNULL( SUBDATE(LEAD(date) OVER (PARTITION BY code ORDER BY date), INTERVAL 1 DAY), @end_dt) as nextdt, value FROM Rate ) SELECT code, TIMESTAMP(dt), val, DAYNAME(dt) FROM dates d LEFT JOIN intervals i ON d.dt BETWEEN i.stdt AND i.spdt ORDER BY code, dt; SELECT * FROM Calendar;