1

For this question (MYSQL select query return list of months as string from between start/end date) I have found solution of query, It gives correct result , BUT I need Month list in Ascending order.

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 |
4  | 2016-06-15 | 2017-11-30 |
------------------------------

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
3  | 2016-06-15 | 2017-11-30 | May-2017 ,November-2016 ,June-2016 ,August-2017 ,March-2017 ,July-2016 ,October-2016 ,November-2017 ,June-2017 ,February-2017 ,September-2016 ,September-2017 ,August-2016,April-2017 ,January-2017 ,July-2017 ,December-2016 ,October-2017
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SQL Query is: 

    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, '%M %Y'))) 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;



It give result randomly like result ie,

 "May-2017 ,November-2016 ,June-2016 ,August-2017 ,March-2017 ,July-2016 ,October-2016 ,November-2017 ,June-2017 ,February-2017 ,September-2016 ,September-2017 ,August-2016,April-2017 ,January-2017 ,July-2017 ,December-2016 ,October-2017"

BUT I need 

"June-2016 ,July-2016 ,August-2016,September-2016,October-2016, November-2016 ,December-2016 ,January-2017 ,February-2017 ,March-2017 ,April-2017 ,May-2017 ,June-2017 ,July-2017 ,August-2017 ,September-2017 ,October-2017, November-2017
"

Please help me to find solution about above result,

Community
  • 1
  • 1
Divyesh K
  • 119
  • 8
  • 1
    If I understand you correctly, you can just add an `order by` inside your `group_concat`, e.g. `group_concat(distinct(DATE_FORMAT(aDate, '%M %Y')) order by year(adate), month(adate))`. – Solarflare Jun 15 '16 at 08:50
  • It is refreshing to see data coming out of a query that way, as opposed to stored in a table that way :p – Drew Jun 15 '16 at 14:25

0 Answers0