SQL query to return quarterly dates from list of dates

Question

Can someone help me with the SQL query? I have a table with has data like this

Periodtable:

ID
StartDate
EndDate

The data will look like this:

1 1/3/2017 2/2/2017
2 2/3/2017 3/2/2017
3 3/3/2017 4/2/207
4 4/3/2017 5/2/2017
5 5/3/2017 6/2/2017
6 6/3/2017 7/2/2017
7 7/3/2017 8/2/2017
8 8/3/2017 9/2/2017
9 9/3/2017 10/2/2017

...

I want to write a SQL query which returns the following 3 columns:

9,8,7 | 7/3/2017 | 10/2/2017
6,5,4 | 4/3/2017 | 7/2/2017
3,2,1 | 1/3/2017 | 4/2/2017

Answer 1

The SQL below using the xml path trick to fold the Id's into a list, grouped by quarter.

select 
stuff(
 (select concat(',',tid.ID)
  from PeriodTable tid
  where datepart(quarter,tid.Startdate) = datepart(quarter,t.Startdate)
    and datepart(year,tid.Startdate) = datepart(year,t.Startdate)
  for xml path(''), type).value('.', 'varchar(max)')
 ,1,1,'') IdList,
min(StartDate) as FirstStartDate, max(EndDate) as LastEndDate
from PeriodTable t
group by datepart(year,Startdate), datepart(quarter,Startdate)
order by FirstStartDate;

Or this variation of the method that uses a CROSS APPLY:

select 
stuff(max(Ids),1,1,'') as IdList, min(StartDate) as FirstStartDate, max(EndDate) as LastEndDate
from PeriodTable t
cross apply (
   select concat(',',tid.ID)
   from PeriodTable tid
   where datepart(quarter,tid.Startdate) = datepart(quarter,t.Startdate)
     and datepart(year,tid.Startdate) = datepart(year,t.Startdate)
   for xml path('')) List(Ids)
group by datepart(year,Startdate), datepart(quarter,Startdate)
order by FirstStartDate;

Try it here on rextester