0

Hi I have a table/query in Mysql like this :

ActivityId | ActivityName | Start     | End        | 
1          | A            |05-06-2010 | 10-09-2010 |
2          | B            |01-09-2012 | 10-05-2013 |

I want to generate months and repeat other data between a start and end date. Like This

ActivityId | ActivityName | Start     | End        | Month   |
1          | A            |05-06-2010 | 10-09-2010 | 06-2010 |
1          | A            |05-06-2010 | 10-09-2010 | 07-2010 |
1          | A            |05-06-2010 | 10-09-2010 | 08-2010 |
1          | A            |05-06-2010 | 10-09-2010 | 09-2010 |
2          | B            |01-09-2012 | 10-05-2013 | 09-2012 |
2          | B            |01-09-2012 | 10-05-2013 | 10-2012 |
2          | B            |01-09-2012 | 10-05-2013 | 11-2012 |
2          | B            |01-09-2012 | 10-05-2013 | 12-2012 |
2          | B            |01-09-2012 | 10-05-2013 | 01-2013 |
2          | B            |01-09-2012 | 10-05-2013 | 02-2013 |
2          | B            |01-09-2012 | 10-05-2013 | 03-2013 |
2          | B            |01-09-2012 | 10-05-2013 | 04-2013 |
2          | B            |01-09-2012 | 10-05-2013 | 05-2013 |
Sitepose
  • 304
  • 1
  • 13

1 Answers1

1

You can use this query (based on the answer to this question) to generate all the months between the start and end month, along with the corresponding values from your activity table:

SELECT ActivityId, ActivityName, Start, End, 
       DATE_FORMAT(Start + INTERVAL t1.i*10 + t0.i MONTH, '%m-%Y') AS Month
FROM
  (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0
CROSS JOIN
  (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1
JOIN activity ON DATE_FORMAT(Start + INTERVAL t1.i*10 + t0.i MONTH, '%Y-%m') <= DATE_FORMAT(End, '%Y-%m')
ORDER BY ActivityId, DATE_FORMAT(Start + INTERVAL t1.i*10 + t0.i MONTH, '%Y-%m')

Output:

ActivityId | ActivityName | Start     | End        | Month   |
1          | A            |05-06-2010 | 10-09-2010 | 06-2010 |
1          | A            |05-06-2010 | 10-09-2010 | 07-2010 |
1          | A            |05-06-2010 | 10-09-2010 | 08-2010 |
1          | A            |05-06-2010 | 10-09-2010 | 09-2010 |
2          | B            |01-09-2012 | 10-05-2013 | 09-2012 |
2          | B            |01-09-2012 | 10-05-2013 | 10-2012 |
2          | B            |01-09-2012 | 10-05-2013 | 11-2012 |
2          | B            |01-09-2012 | 10-05-2013 | 12-2012 |
2          | B            |01-09-2012 | 10-05-2013 | 01-2013 |
2          | B            |01-09-2012 | 10-05-2013 | 02-2013 |
2          | B            |01-09-2012 | 10-05-2013 | 03-2013 |
2          | B            |01-09-2012 | 10-05-2013 | 04-2013 |
2          | B            |01-09-2012 | 10-05-2013 | 05-2013 |

Demo on SQLFiddle

Nick
  • 138,499
  • 22
  • 57
  • 95
  • can you help me set collation for my newly added Month column. Its creating problem when saving as view. – Sitepose Jul 05 '20 at 07:04
  • @Sitepose what problem are you seeing? Can you make a demo out of my fiddle? – Nick Jul 05 '20 at 07:06
  • Actually when I save this query as a view, It sets the month column collation as utf8mb4_unicode_ci while my other columns have utf8_unicode_ci. And when I further compare this column with other columns a new error comes : #1267 - Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '=' – Sitepose Jul 05 '20 at 07:13
  • @Sitepose I can't reproduce... https://www.db-fiddle.com/f/bU7zphQoMnKog8MmgagbFm/0 – Nick Jul 05 '20 at 07:39
  • 1
    You could try `CONVERT(DATE_FORMAT(Start + INTERVAL t1.i*10 + t0.i MONTH, '%m-%Y') USING utf8) COLLATE utf8_unicode_ci AS Month` – Nick Jul 05 '20 at 07:54
  • I tried by adding this in above query, But it shows syntax error : Unrecognised keyword – Sitepose Jul 05 '20 at 08:38
  • 1
    @Sitepose working here: https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=2d4fa276d8260c26dbf6de4680d2dcd0 – Nick Jul 05 '20 at 09:01
  • Yes It is working . Thanks . Syntax error earlier because I was mistakenly only executing query without creating view. – Sitepose Jul 05 '20 at 09:14