mysql date breakdown between date range

Question

I have a mySQL record like this

name    sdate       edate  
John    2013-12-01  2013-12-04
Will    2013-12-04  2013-12-06
Smith   2013-12-02  2013-12-05

I need a single query for result like this

name    stay_date
John    2013-12-01
John    2013-12-02
John    2013-12-03
John    2013-12-04
Will    2013-12-04
Will    2013-12-05
Will    2013-12-06
Smith   2013-12-02
Smith   2013-12-03
Smith   2013-12-04
Smith   2013-12-05

Thanx alot in advance..

take a loot at this, same question: http://stackoverflow.com/a/10432083/1675233 — Mehdi, Dec 10 '13 at 05:46

score 0 · Answer 1 · answered Dec 10 '13 at 07:41

For this you'll need a tally(numbers) table, that you can create and populate in the following way

CREATE TABLE tally(n INT NOT NULL PRIMARY KEY);
INSERT INTO tally(n)
SELECT a.N + b.N * 10 + 1 n
 FROM 
(SELECT 0 AS 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) a
,(SELECT 0 AS 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) b
ORDER BY n;

Now your query might look like

SELECT name, sdate + INTERVAL n.n - 1 DAY stay_date
  FROM table1 t JOIN tally n
    ON n.n <= edate - sdate + 1

Output:

+-------+------------+
| name  | stay_date  |
+-------+------------+
| John  | 2013-12-01 |
| John  | 2013-12-02 |
| John  | 2013-12-03 |
| John  | 2013-12-04 |
| Will  | 2013-12-04 |
| Will  | 2013-12-05 |
| Will  | 2013-12-06 |
| Smith | 2013-12-02 |
| Smith | 2013-12-03 |
| Smith | 2013-12-04 |
| Smith | 2013-12-05 |
+-------+------------+

Here is SQLFiddle demo

mysql date breakdown between date range

1 Answers1