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
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;
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments