MYSQL select query return list of months as string from between start/end date

Question

There are two main fields Startdate and Enddate in Contract table I want to give result as extra column called description to represent month come between Contract start and end. see bellow table and result which I require

Table : Contracts
------------------------------
ID | START      | END        |
------------------------------
1  | 2016-05-01 | 2016-07-31 |
2  | 2016-04-01 | 2016-08-31 |
3  | 2016-01-22 | 2016-02-25 |
------------------------------

Here I need result as per bellow formate, one extra field which represent range/list of months between startdate and enddate of contract using SELECT query.

Result (as per give format)
----------------------------------------------------------------------------------------
ID | START      | END        | Description      
----------------------------------------------------------------------------------------
1  | 2016-05-01 | 2016-07-31 | May-2016, Jun-2016, July-2016
2  | 2016-04-01 | 2016-07-31 | April-2016, May-2016, Jun-2016, July-2016
3  | 2016-01-22 | 2016-02-25 | January-2016, February-2016
----------------------------------------------------------------------------------------

Example first row of above table startdate is 2016-05-01 (2016-May-01) and end date is 2016-07-31 (2016-July-31) so here it gives list of months and year between May-01 to July-31 so description will be May-2016, Jun-2016, July-2016.

I tried many queries still I fail to get the exact SQL query.

Don't know how to do exactly and get same result,

Any suggestions please

Thanks in Advance

You might want to consider creating a [calendar table](http://stackoverflow.com/questions/14105018/generating-a-series-of-dates). The reason for this is that some of the month values in your `Description` column are not actually present in your original table. — Tim Biegeleisen, May 09 '16 at 05:56
Can I create one extra table called calendar table or is it temporary table ? — Divyesh K, May 09 '16 at 06:05
This depends on whether you plan to do this sort of query often. — Tim Biegeleisen, May 09 '16 at 06:09
Its not quite possible with mysql only you should also use php for that — Bhaskar Bhatt, May 09 '16 at 07:23
If they are just in one year, it could be easy for mysql's implement, if not, I think you'd better handle this by java or php or other language. — Mippy, May 09 '16 at 08:04
for one year only or ma be for next year not more than two year — Divyesh K, May 09 '16 at 09:29

score 1 · Accepted Answer · answered May 09 '16 at 10:25

The below query should do the trick.

Your data in input and output are different for 2nd row, I have run it for the output data

select id, DATE_FORMAT(start_Date, '%Y-%c-%d') as Start_Date,
       DATE_FORMAT(end_date,'%Y-%c-%d') as END_Date,
       group_concat( distinct(DATE_FORMAT(aDate, '%Y %M '))) as Descp
from (
     select ss.end_date - interval (a.a ) month as aDate from
     (select 0 as a 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, Contracts ss 
     ) mon, Contracts sa
where aDate between sa.start_date and sa.end_date
group by id;

above query give me random result ie, 2016 March , 2016 January, 2016 February , But I need **2016 January, 2016 February, 2016 March** — Divyesh K, Jun 15 '16 at 06:37

MYSQL select query return list of months as string from between start/end date

1 Answers1

Linked