mySQL - Generate Additional Lines Between Dates

Question

I have a table like the below.

id     month     duration
001    1/1/16    3
002    3/1/16    4
003    12/1/15   2   

I would like to add a new row to the table for every month after the month shown for the number specified minus 1, e.g. below:

id     month    duration
001    1/1/16   3
001    2/1/16   3
001    3/1/16   3
002    3/1/16   4
002    4/1/16   4
002    5/1/16   4
002    6/1/16   4
003    12/1/15  2
003    1/1/16   2

And so on, while duplicating the values in any column not shown.

I have done this in R, where I first populated 'short' data and then reshaped it to long, but after searching online, I still have no idea how to do this in mySQL. Thanks in advance for your help!

Answer 1

If you could automatically generate rows in MySQL that would be pretty easy; except that you can't. Still, you could use the technique described here to generate some rows and then use a JOIN to get the data you need:

-- Create a VIEW with 16 dummy rows
CREATE OR REPLACE VIEW generator_16
AS SELECT 0 n UNION ALL SELECT 1  UNION ALL SELECT 2  UNION ALL 
   SELECT 3   UNION ALL SELECT 4  UNION ALL SELECT 5  UNION ALL
   SELECT 6   UNION ALL SELECT 7  UNION ALL SELECT 8  UNION ALL
   SELECT 9   UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL
   SELECT 12  UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL 
   SELECT 15;

Having this generator, your query becomes:

SELECT id, date_add(month, INTERVAL n MONTH), duration
FROM tbl
INNER JOIN generator_16 ON n < duration
ORDER BY id, month

I assumed your table structure is the following:

CREATE TABLE tbl(id varchar(10), month date, duration int);
INSERT INTO tbl VALUES('001', '2016-01-01', 3);
INSERT INTO tbl VALUES('002', '2016-03-01', 4);
INSERT INTO tbl VALUES('003', '2015-12-01', 2);

Answer 2

Issues of data display are generally best resolved in the presentation layer (e.g. a simple PHP loop), but just for fun...

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,dt DATE NOT NULL
,duration INT NOT NULL
);

INSERT INTO my_table VALUES
(1,'2016-01-01',3),
(2,'2016-03-01',4),
(3,'2015-12-01',2);   

SELECT * FROM my_table;
+----+------------+----------+
| id | dt         | duration |
+----+------------+----------+
|  1 | 2016-01-01 |        3 |
|  2 | 2016-03-01 |        4 |
|  3 | 2015-12-01 |        2 |
+----+------------+----------+

SELECT * FROM ints;
+---+
| i |
+---+
| 0 |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
+---+

 SELECT x.id,x.dt + INTERVAL y.i MONTH,x.duration FROM my_table x JOIN ints y ON y.i < x.duration;
+----+---------------------------+----------+
| id | x.dt + INTERVAL y.i MONTH | duration |
+----+---------------------------+----------+
|  1 | 2016-01-01                |        3 |
|  1 | 2016-02-01                |        3 |
|  1 | 2016-03-01                |        3 |
|  2 | 2016-03-01                |        4 |
|  2 | 2016-04-01                |        4 |
|  2 | 2016-05-01                |        4 |
|  2 | 2016-06-01                |        4 |
|  3 | 2015-12-01                |        2 |
|  3 | 2016-01-01                |        2 |
+----+---------------------------+----------+