title: SQL Server: only group consecutive records when using GROUP BY
body: <p>I have a table that contains a record for each ward stay within a hospital spell (note: a spell can include transfers to other hospitals). Spellno is the unique identifier of a spell.  I would like to aggregate consecutive ward stays within a spell to hospital level. The problem I have is that if a patient goes from hospital1 to hospital2 and back to hospital1 a <code>GROUP BY</code> 'Spellno' and 'Hospital' would combine the two hospital1 stays, which I don't want to do.</p>
  
  <p>e.g. if this was my data:</p>
  
  <pre><code>Spellno   Hospital   WardCode   WardStart   WardEnd 
  -------------------------------------------------------------------
  123       hosp1      ward1      01/04/2015  03/04/2015
  123       hosp1      ward4      03/04/2015  05/04/2015
  123       hosp2      ward2      05/04/2015  07/04/2015
  123       hosp1      ward3      07/04/2015  10/04/2015
  123       hosp1      ward1      10/04/2015  12/04/2015
  
  </code></pre>
  
  <p>I want to aggregate on Spellno and Hospital to get:</p>
  
  <pre><code>Spellno   Hospital   WardStart   WardEnd 
  -------------------------------------------------------------------
  123       hosp1      01/04/2015  05/04/2015
  123       hosp2      05/04/2015  07/04/2015
  123       hosp1      07/04/2015  12/04/2015
  </code></pre>
  
  <p>Many thanks in advance.</p>