SQL-запрос для возврата квартальных дат из списка дат

Question

Может кто-нибудь помочь мне с запросом SQL? У меня есть таблица с такими данными

Периодическое расписание:

ID
StartDate
EndDate

Данные будут выглядеть так:

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

...

Я хочу написать запрос SQL, который возвращает следующие 3 столбца:

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

В приведенном ниже SQL-коде используется трюк с XML-путём, чтобы сложить идентификаторы в список, сгруппированный по кварталам.

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;

Или этот вариант метода, который использует 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;

Попробуйте здесь на rextester