SQL - combine consecutive date rows based on column

Question

Let's say I have the following SQL result

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

I need to group all months with the same quantity into one row so the result should be

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

I've searched around stackoverflow and found similar posts which solution's I've tried to no avail This and This

The following is the SQL i'm using to create the result where term_start would be my begtime and term_end would be my endtime. Please let me know if you have any suggestions. This is a dataset I'm using in my SSRS reports, if there's an easier way on the SSRS reporting side to do this then I'm open to options.. THANKS!

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

score 2 · Answer 1 · answered Aug 31 '15 at 20:06

2

You can use a recursive CTE starting with the first row:

;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 #t t 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;

I loaded the sample data like so:

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);

answered Aug 31 '15 at 20:06

Kevin Suchlicki

3,096
2
15
17

that worked!! you're a lifesaver! I tried to vote up your solution but it says my reputation is too low – Jack89515 Aug 31 '15 at 21:46
This only works if the rows always start at the 1st of the month and end at the last of the month and there are no gaps. Not sure if that's the case for @user3915771 – James Z Sep 01 '15 at 03:17
this actually blows up when the result is more than 20 rows – Jack89515 Nov 05 '15 at 22:30
@Jack89515 I don't see any reason why there should be anything magical about the number "20" in causing it to blow up. There *could* be something else wrong. What is the error you are getting? You may have hit the max recursion limitation, so try adding "option (maxrecursion 0)" before the final semi-colon. – Kevin Suchlicki Nov 05 '15 at 22:53
@Kevin there is nothing magical about the number 20, what I was saying is that as the table get bigger the processing time grows exponentially for some reason. In my case, if the result is under 10 rows, I get an instant query result, at 21 rows the sql server return time is 7 seconds, any more rows after that it just takes forever, eat up all the server processing power until I kill the process – Jack89515 Nov 05 '15 at 23:00
@Jack89515 It's a recursive process, so it certainly has the potential to run wild, but for any "normal" data, this should not be a limitation. In particular, that small a number of rows should pose no problem. I have used similar techniques on tables with millions of rows with very fast return times. There could be some kind of infinite cycle it's getting into... Do you want to update your original question with the new data and I might be able to look at it tomorrow? – Kevin Suchlicki Nov 05 '15 at 23:07

score 1 · Answer 2 · answered Sep 02 '15 at 04:44

check this

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

SQL - combine consecutive date rows based on column

2 Answers2

Linked