MYSQL Including Missing Values Using the Previous Most Recent Record

MySQL

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.

Expected Output

enter image description here

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

enter image description here

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)

Best Answer

A correct form of the query would be:

SELECT
    t1.date,
    (SELECT v1.type_id FROM tbl_values v1 where v1.type_id = 100 AND v1.date <= t1.date ORDER BY v1.date desc limit 1) as `type`,
    (SELECT v1.`value` FROM tbl_values v1 where v1.type_id = 100 AND v1.date <= t1.date ORDER BY v1.date desc limit 1) as `value`

FROM tbl_calendar t1
having `type` IS NOT NULL

with result as:

2016-12-02  100 1.00
2016-12-03  100 1.00
2016-12-04  100 2.00
2016-12-05  100 2.00
2016-12-06  100 3.00
2016-12-07  100 3.00
2016-12-08  100 4.00
2016-12-09  100 4.00
2016-12-10  100 5.00

but, again look at your query and your expected result please.

What do you want to have in the result (type, date, value) with:

SELECT 
    c.date,

    o1.value_id AS "o1.value_id",
    o2.value_id AS "o2.value_id",

    o1.type_id AS "o1.type_id",
    o2.type_id AS "o2.type_id",

    o1.date AS "o1.date",
    o2.date AS "o2.date"

query plan