SQL – объединение последовательных строк даты на основе столбца

Предположим, у меня есть следующий результат SQL

BegDate | EndDate | quanitty 1/1/2014 1/31/2014 1 2/1/2014 2/28/2014 1 3/1/2014 3/31/2014 2 4/1/2014 4/30/2014 4 5/1/2014 5/31/2014 4 6/1/2014 6/30/2014 4 7/1/2014 7/31/2014 2 8/1/2014 8/30/2014 2 

Мне нужно группировать все месяцы с одинаковым количеством в одну строку, чтобы результат был

 BegDate | EndDate | quanitty 1/1/2014 2/28/2014 1 3/1/2014 3/31/2014 2 4/1/2014 6/30/2014 4 7/1/2014 8/30/2014 2 

Я искал вокруг stackoverflow и нашел похожие сообщения, решение которых я пробовал безрезультатно. This and This

• Как показать промежуточное и общее количество групп в ssrs
• Конструктор отчетов SSRS 2012 не запускается с удаленного хоста - 401 Несанкционированный
• SSRS - повторное использование временной таблицы в нескольких наборах данных
• Создатель отчетов SSRS, как скрыть конкретное текстовое поле, экспортирующее в Excel
• Несколько отчетов в SSRS с использованием той же хранимой процедуры

Ниже приводится SQL, который я использую для создания результата, где term_start будет моим begtime, и term_end будет моим конечным временем. Пожалуйста, дайте мне знать, если у вас есть предложения. Это набор данных, который я использую в своих отчетах SSRS, если в отчетной части SSRS есть более простой способ сделать это, тогда я открыт для опций .. СПАСИБО!

 SELECT collaboration = t.collaboration , trade = t.trade , position = p.position , buyer = CASE WHEN ((p.positiontype = 'BUY') OR (p.positiontype = 'SWAP' AND p.swaptype = 'BUY')) THEN co.name WHEN ((p.positiontype = 'SELL') OR (p.positiontype = 'SWAP' AND p.swaptype = 'SELL')) THEN cp.name END , seller = CASE WHEN ((p.positiontype = 'SELL') OR (p.positiontype = 'SWAP' AND p.swaptype = 'SELL')) THEN co.name WHEN ((p.positiontype = 'BUY') OR (p.positiontype = 'SWAP' AND p.swaptype = 'BUY')) THEN cp.name END , trade_date = t.tradedate , timezone = t.timezone , delivery_point = isnull(pp.point,'') ------------TABLE DATA BELOW------------ , currency = f.currency , currency_unit = f.unit , term_start = CASE WHEN pq.quantitystatus = 'TRADE' THEN pq.begtime ELSE 0 END , term_end = CASE WHEN pq.quantitystatus = 'TRADE' THEN dateadd(dd,-1,pq.endtime) ELSE 0 END , hours = CASE WHEN ls.loadshape is not null and ls.granularity = 'LOAD' and lsq.loadshape is not null THEN 'Hour Ending (HE) ' + CONVERT(VARCHAR,lsq.beghour + 1) + '00 through HE ' + CONVERT(VARCHAR, lsq.endhour) + '00 (' + CONVERT(VARCHAR, lsq.endhour-lsq.beghour-1) +' Hours each day), Monday Through Sunday, including NERC holidays; ' + tz.description WHEN ls.loadshape is not null and ls.granularity = 'LOAD' and lsq.loadshape is null THEN 'See attached schedule' WHEN ls.loadshape is not null and ls.granularity = 'PROFILE' THEN 'See attached schedule' ELSE btz.cpn_description END , quantity = CASE WHEN ls.loadshape is not null and ls.granularity = 'LOAD' and lsq.loadshape is not null THEN CONVERT(VARCHAR,convert(double precision, lsq.quantity)) WHEN ls.loadshape is not null and ls.granularity = 'LOAD' and lsq.loadshape is null THEN 'See attached schedule' WHEN ls.loadshape is not null and ls.granularity = 'PROFILE' THEN 'See attached schedule' ELSE CONVERT(VARCHAR,convert(double precision, pq.energy)) END , product = pr.producttype , unit = CASE isnull(p.unit,'') WHEN 'dth' THEN 'Dth' WHEN 'kwh' THEN 'kWh' WHEN 'kwm' THEN 'kWm' WHEN 'mmbtu' THEN 'MMBtu' WHEN 'mw' THEN 'MW' when 'mwd' THEN 'MWD' WHEN 'mwh' THEN 'MWH' WHEN 'mwm' THEN 'MWM' WHEN 'gal' THEN 'Gallon' ELSE isnull(p.unit,'') END , unit_plural = CASE isnull(p.unit,'') WHEN 'dth' THEN 'Dth' WHEN 'kwh' THEN 'kWh' WHEN 'kwm' THEN 'kWm' WHEN 'mmbtu' THEN 'MMBtu''s' WHEN 'mw' THEN 'MW' when 'mwd' THEN 'MWD' WHEN 'mwh' THEN 'MWH' WHEN 'mwm' THEN 'MWM' WHEN 'gal' THEN 'Gallons' ELSE isnull(p.unit,'') END , total_qty = convert(double precision, pp.totalquantity) , priceindex = f.priceindex , pricediff = convert(double precision, f.pricediff) , price = CASE WHEN t.tradetype = 'Phys Power HR' or t.tradetype = 'Phys HR Power Option' and f.priceindex is not null THEN priceindex + '*' + convert(varchar, convert(double precision, factor)) WHEN t.tradetype = 'Phys Power HR' or t.tradetype = 'Phys HR Power Option' and f.priceindex is null THEN convert(varchar, convert(double precision, f.pricediff)) WHEN f.priceindex is not null and f.pricediff is null THEN f.priceindex WHEN f.priceindex is not null and isnull(CONVERT(FLOAT, f.pricediff), 0) > 0 THEN f.priceindex + '+' + convert(varchar(max), convert(double precision, f.pricediff)) WHEN f.priceindex is not null and isnull(CONVERT(FLOAT, f.pricediff), 0) < 0 THEN f.priceindex + '-' + convert(varchar(max), convert(double precision, f.pricediff)) ELSE convert(varchar(max), convert(double precision, f.pricediff)) END , loadshape = p.loadshape , lstimeunit = ls.timeunit , granularity = ls.granularity , loadshapeHours = 'Hour Ending (HE) ' + CONVERT(VARCHAR,lsq.beghour + 1) + '00 through HE ' + CONVERT(VARCHAR, lsq.endhour) + '00 (' + CONVERT(VARCHAR, lsq.endhour-lsq.beghour-1) +' Hours each day), Monday Through Sunday, including NERC holidays; ' + tz.description , loadshapeQuantity = convert(double precision, lsq.quantity) , loadshapeQuantityLoadshape = lsq.loadshape , loadshapeFlag = CASE WHEN ls.loadshape is not null and ls.granularity = 'LOAD' and lsq.loadshape is null THEN 1 WHEN ls.loadshape is not null and ls.granularity = 'PROFILE' THEN 1 ELSE 0 END FROM trade t INNER JOIN (SELECT *, CASE positiontype WHEN 'BUY' THEN 'PAY' WHEN 'SELL' THEN 'REC' END paystatus from position) p on p.trade = t.trade INNER JOIN product pr on p.product = pr.product INNER JOIN powerposition pp on p.position = pp.position INNER JOIN powerquantity pq on pq.position = pp.position AND pq.posdetail = pp.posdetail AND pq.quantitystatus = 'TRADE' INNER JOIN blocktimezone btz on btz.block = p.block and t.timezone = btz.timezone LEFT OUTER JOIN fee f on f.dbvalue = p.position AND f.dbcolumn = 'POSITION' AND f.feemethod = 'COMMODITY PRICE' AND f.feemode in ('FIXED', 'VARIABLE') AND f.feetype IS NULL INNER JOIN counterparty co on co.counterparty = t.company INNER JOIN counterparty cp on cp.counterparty = p.counterparty LEFT JOIN loadshape ls on ls.loadshape = p.loadshape LEFT JOIN loadshapequantity lsq on lsq.loadshape = ls.loadshape LEFT JOIN timezone tz on tz.timezone = t.timezone WHERE 1=1 AND p.positionmode = 'PHYSICAL' AND t.collaboration = @collaboration 

 ;WITH CTE AS ( SELECT TOP 1 *, 1 AS Id FROM #t ORDER BY BegDate UNION ALL SELECT t.*, c.Id + CASE WHEN t.quanitty = c.quanitty THEN 0 ELSE 1 END FROM CTE c JOIN #tt ON c.BegDate = DATEADD(MONTH, -1, t.BegDate) ) SELECT MIN(BegDate) AS BegDate, MAX(EndDate) AS EndDate, MIN(quanitty) AS quanitty FROM CTE GROUP BY Id ORDER BY Id; 

 SELECT CAST(t.BegDate AS DATE) AS BegDate , CAST(t.EndDate AS DATE) AS EndDate , CAST(t.quanitty AS INT) AS quanitty INTO #t FROM ( VALUES ('1/1/2014','1/31/2014',1), ('2/1/2014','2/28/2014',1), ('3/1/2014','3/31/2014',2), ('4/1/2014','4/30/2014',4), ('5/1/2014','5/31/2014',4), ('6/1/2014','6/30/2014',4), ('7/1/2014','7/31/2014',2), ('8/1/2014','8/30/2014',2) ) AS t(BegDate,EndDate,quanitty); 

 select min(BegDate) as BegDate, max(EndDate) as EndDate, min(quanitty) as quanitty from ( select *, row_number() over(partition by quanitty order by quanitty) p_rno ,row_number() over(order by BegDate) rno --,row_number() over(partition by quanitty order by quanitty)- -- row_number() over(order by BegDate) rno from #t ) t group by (p_rno-rno) order by BegDate