假设我有以下 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 https://stackoverflow.com/questions/24244659/group-consecutive-rows-of-same-value-using-time-spans and This https://stackoverflow.com/questions/23257246/combining-values-of-a-specific-column-from-consecutive-rows-in-sql
以下是我用来创建结果的 SQL,其中 term_start 是我的开始时间,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