title: SQL - combining consecutive months of the same block with same quantity
body: <p>This question will seem very easy at first but as you start writing the complexity hits. I have attached a picture blow with the result set of my SQL. The result is 39 rows. I need to combine all the consecutive rows of the same block with the same value. With this example, the end result should be 29 rows where all the red box'd rows below should be consolidated into 1 row.</p>
  
  <p>so for example the first redbox with quantity = 40 should combine into 1 row with term_start = 2017-06-01 and term_end = 2017-08-01</p>
  
  <p><a href="https://i.stack.imgur.com/kunUe.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/kunUe.png" alt="enter image description here"></a></p>
  
  <p>Here's my Code</p>
  
  <pre><code>SELECT
  pp.position
  , term_start = pq.begtime
  , term_end = pq.endtime
  , quantity = CONVERT(VARCHAR,convert(double precision, pq.energy))  
  , block = p.block           
  FROM trade t
     INNER JOIN position p on p.trade = t.trade
     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'
  WHERE 1=1
  AND p.positionmode = 'PHYSICAL'
  AND t.collaboration = 13119572 
  </code></pre>
  
  <p>I've been stuck on this problem for three days straight now. I've explored using CTEs and Row_Number() over () but with no success. Any help would be greatly appreciated!! </p>