MySQL Query duplicate row by x times while increment date

Question

I have a database that manages courses for a watersports centre. I have a relatively complex SQL query which will create a table with the following columns among others.

"Course title", "Start Date", "Duration (Days)"

eg.

 XYZ Sailing Course       12/01/17    3  
 ABC Windsurfing Course   13/01/17    2

I want to create a view/query which has a row for each day that a course is course. So for the above would display

  XYZ Sailing Course       12/01/17 
  XYZ Sailing Course       13/01/17
  ABC Windsurfing Course   13/01/17     
  XYZ Sailing Course       14/01/17 
  ABC Windsurfing Course   14/01/17

Luckily if a course runs across several days it's at the same time, so it is only the date field which needs to change in the otherwise duplicate rows. Also the days are always sequential, if a course lasted several days there would never be a gap in the middle. Is there an elegant way to create this output I have not thought of (or even a messy one - I'm stuck!)

Many thanks in advance for any light you might shed.

Do you really have to do it in SQL? This kind of thing is much easier in the client programming language. — Barmar, Jan 12 '17 at 21:05
Look for ideas: http://stackoverflow.com/questions/2157282/generate-days-from-date-range — PM 77-1, Jan 12 '17 at 21:06
The ideas there should work. Just use `DATE_ADD(start_date, INTERVAL duration-1 DAY)` as the end date for the range. — Barmar, Jan 12 '17 at 21:07
@Barmar - yeah - unfortunately to things it has to link to need to be it to be SQL rows. I could manage it in php, or outputting to csv or whatever. I'll try the link you suggest and let you know how I get on. — Taffy, Jan 12 '17 at 22:50

score 3 · Answer 1 · answered Jan 13 '17 at 18:08

OK, it was a bit messy, but I solved it thanks to the help of @barmar

I jiggled the rows in my original query around to give me a "start date" and "finish date" column, then did the following

SELECT 
        `a`.`Date` AS `Date`,
        Other rows required go here
    FROM
        ((SELECT 
            (CURDATE() - INTERVAL ((`a`.`a` + (10 * `b`.`a`)) + (100 * `c`.`a`)) DAY) AS `Date`
        FROM
            ((((SELECT 0 AS `a`) UNION ALL SELECT 1 AS `1` UNION ALL SELECT 2 AS `2` UNION ALL SELECT 3 AS `3` UNION ALL SELECT 4 AS `4` UNION ALL SELECT 5 AS `5` UNION ALL SELECT 6 AS `6` UNION ALL SELECT 7 AS `7` UNION ALL SELECT 8 AS `8` UNION ALL SELECT 9 AS `9`) `a`
        JOIN (SELECT 0 AS `a` UNION ALL SELECT 1 AS `1` UNION ALL SELECT 2 AS `2` UNION ALL SELECT 3 AS `3` UNION ALL SELECT 4 AS `4` UNION ALL SELECT 5 AS `5` UNION ALL SELECT 6 AS `6` UNION ALL SELECT 7 AS `7` UNION ALL SELECT 8 AS `8` UNION ALL SELECT 9 AS `9`) `b`)
        JOIN (SELECT 0 AS `a` UNION ALL SELECT 1 AS `1` UNION ALL SELECT 2 AS `2` UNION ALL SELECT 3 AS `3` UNION ALL SELECT 4 AS `4` UNION ALL SELECT 5 AS `5` UNION ALL SELECT 6 AS `6` UNION ALL SELECT 7 AS `7` UNION ALL SELECT 8 AS `8` UNION ALL SELECT 9 AS `9`) `c`)) `a`
        JOIN `MyDB`.`All Courses`)
    WHERE
        (`a`.`Date` BETWEEN `All Courses`.`Start Date` AND `All Courses`.`Finish Date`)

Where "All Courses" is the SQL view with all the other row in. Thanks guys, all fixed!

MySQL Query duplicate row by x times while increment date

1 Answers1