Select a sequence between two numbers on MySQL
date, mysql, sql, sql-view
Solution
Something like this should do it:-
SELECT a.Name, GROUP_CONCAT(YEAR(DATE_ADD(a.start_date, INTERVAL b.aNum YEAR))) AS activity_years
FROM person a
CROSS JOIN (SELECT a.i + b.i * 10 AS aNum FROM integers a, integers b) b
WHERE YEAR(DATE_ADD(a.start_date, INTERVAL b.aNum YEAR)) <= YEAR(a.end_date)
GROUP BY a.Name
It relies on a table of integers with a column called i, with the values 0 to 9. It joins this against itself to get a range of numbers from 0 to 99, so copes with date ranges that far apart.
Removing the subselects to use it in a view
SELECT p.Name, GROUP_CONCAT(YEAR(DATE_ADD(p.start_date, INTERVAL (a.i + b.i * 10) YEAR))) AS activity_years
FROM person p
CROSS JOIN integers a
CROSS JOIN integers b
WHERE YEAR(DATE_ADD(p.start_date, INTERVAL (a.i + b.i * 10) YEAR)) <= YEAR(p.end_date)
GROUP BY p.Name
Problem
I have this table named `people` with two dates on MySQL: ``` | Name | start_date | end_date | | John | 2007-03-01 | 2009-10-12 | | Mike | 2001-06-06 | 2010-12-01 | ``` I want to create a view that lets me search by activity year, being activity year any year between the `start_date` and the `end_date`. So, I'd like to get a field with a sequence of years, like this: ``` | Name | activity_years | | John | 2007,2008,2009 | | Mike | 2001,2002,2003,2004,2005,2006,2007,2008,2009,2010 | ``` I've tried some approaches, but I can't get it. Since I want to create a view, I have to do it everything inside a SELECT statement and that is giving me some headache.