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 ); select * from Rate; 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); select * from Rate; create table Calendar (dDay datetime not null, weekday VARCHAR(10) not null); select * from Calendar; WITH RECURSIVE nrows(date) AS ( SELECT '2009-06-05' UNION ALL SELECT DATE_ADD(date,INTERVAL 1 day) FROM nrows WHERE date<='2009-06-18' ) SELECT date, DAYNAME(date) FROM nrows WHERE DAYNAME(date) NOT LIKE 'Saturday' AND DAYNAME(date) NOT LIKE 'Sunday' ; INSERT INTO Calendar(dDay, weekday) WITH RECURSIVE nrows(date) AS ( SELECT '2009-06-05' UNION ALL SELECT DATE_ADD(date,INTERVAL 1 day) FROM nrows WHERE date<='2009-06-18' ) SELECT date, DAYNAME(date) FROM nrows WHERE DAYNAME(date) NOT LIKE 'Saturday' AND DAYNAME(date) NOT LIKE 'Sunday' ; SELECT * FROM Calendar; WITH ntable (code, dDay, value, weekday) AS ( SELECT code, dDay, value, weekday FROM Calendar LEFT JOIN (SELECT * FROM Rate WHERE code = 'EUR') as RateE ON Calendar.dDay = RateE.date UNION ALL SELECT code, dDay, value, weekday FROM Calendar LEFT JOIN (SELECT * FROM Rate WHERE code = 'USD') as RateU ON Calendar.dDay = RateU.date ) SELECT LAG(code) OVER(ORDER BY dDay), dDay FROM ntable group by code, dDay ; WITH nt1 (code, dDay, value, weekday) AS ( SELECT 'EUR' as code, dDay, value, weekday FROM Calendar LEFT JOIN (SELECT * FROM Rate WHERE code LIKE 'EUR') as RateE ON Calendar.dDay = RateE.date ), nt2 (code, dDay, value, weekday) AS ( SELECT 'USD' as code, dDay, value, weekday FROM Calendar LEFT JOIN (SELECT * FROM Rate WHERE code LIKE 'USD') as RateU ON Calendar.dDay = RateU.date ), nt3 (code, dDay, value, weekday) AS ( SELECT code, dDay, value, weekday FROM nt1 UNION ALL SELECT code, dDay, value, weekday FROM nt2 ) SELECT code, dDay, value, LAG(value) OVER(PARTITION BY code ORDER BY dDay), weekday FROM nt3 ; SELECT (SELECT Rate.code FROM Rate WHERE Rate.code LIKE('EUR') AND Rate.date < Calendar.dDay ORDER by Calendar.dDay LIMIT 1) as code, Calendar.dDay, (SELECT Rate.value FROM Rate WHERE Rate.code LIKE('EUR') AND Rate.date < Calendar.dDay ORDER by Calendar.dDay LIMIT 1) as value, Calendar.weekday FROM (SELECT DISTINCT code, value FROM Rate) as tmp CROSS JOIN Calendar ;