Given a table containing a date for every day over a period
CREATE TABLE `tbl_calendar` ( `date` date NOT NULL, PRIMARY KEY (`date`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; INSERT INTO `tbl_calendar` (`date`) VALUES ('2016-12-10'), ('2016-12-09'), ('2016-12-08'), ('2016-12-07'), ('2016-12-06'), ('2016-12-05'), ('2016-12-04'), ('2016-12-03'), ('2016-12-02'), ('2016-12-01') ;
And a table containing values, of different types, with values missing for random days where they have not been populated.
CREATE TABLE `tbl_values` ( `value_id` int(11) NOT NULL AUTO_INCREMENT, `type_id` int(11) NOT NULL DEFAULT '0', `date` date DEFAULT NULL, `value` double(15,2) DEFAULT '0.00', PRIMARY KEY (`value_id`), KEY `type_id_date` (`type_id`,`date`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; INSERT INTO `tbl_values` (`type_id`, `date`, `value`) VALUES (100, '2016-12-02', 1), (100, '2016-12-04', 2), (100, '2016-12-06', 3), (100, '2016-12-08', 4), (100, '2016-12-10', 5) ;
How can the values for the missing days be returned in a SELECT, using the most recent previous record for that type? here is what I have so far.
SELECT v1.type, c.date, v1.value FROM tbl_calendar c LEFT JOIN tbl_values v1 ON ( v1.type_id = 100 AND v1.date <= c.date ) LEFT JOIN tbl_values v2 ON ( v2.type_id = 100 AND v2.date < c.date AND v2.date > o1.date ) WHERE v1.date = c.date OR v2.date IS NULL
The problem with this query is that it returns the previous most recent records value when there is a value for that date, along with a record with the correct value.
a_vlad answer is correct in results, however poor on performance, which is expected.
SELECT t1.date, (SELECT v1.type_id FROM tbl_values v1 where v1.date <= t1.date ORDER BY v1.date desc limit 1) as `type`, (SELECT v1.`value` FROM tbl_values v1 where v1.date <= t1.date ORDER BY v1.date desc limit 1) as `value` FROM tbl_calendar t1 HAVING `type` IS NOT NULL
The final solution that I used was to use a_vlad query to create a summary table. However, it turned out not to increase performance of the system (as previously the missing values were filled in in a PHP loop, which worked out to be as fast)