title: SQL: make small spans into 1 large continuous span
body: <p>I have some data where after a period of time a type of data can renew.</p>
  
  <pre><code>         ID       Type         Start           End
           1         A            01/01/2017     02/01/2017
           1         A            02/01/2017     03/01/2017
           1         A            03/01/2017     05/01/2017
           1         A            04/01/2017     06/01/2017
           1         A            09/01/2017     10/01/2017
  </code></pre>
  
  <p>I currently do the following</p>
  
  <pre><code>   SELECT 
         A.ID,
         A.Type,
         B.Start,
         A.End
     FROM my_data as A
     INNER JOIN my_data as B
         ON A.ID = B.ID
         AND A.start&gt;B.start
         AND A.start &lt; B.end
     WHERE A.type = B.Type
      AND A.end-B.start &lt;=365;
  </code></pre>
  
  <p>So that I get a data set where if an ID has the same type and the start/end spans have NO gaps between them (but they can overlap) the first start is start and the last end is end (and they don't go beyond the year aka the earliest start is already 01/01/2017 but some ends can go into 2018 we want to stop them on the last day of the year).</p>
  
  <p>Currently we get a data set like this:</p>
  
  <pre><code>     ID       Type        Start            End    
        1        A           01/01/2017      03/01/2017
        1        A            02/01/2017      05/01/2017
        1        A            04/01/2017      06/01/2017
  </code></pre>
  
  <p>And I just keep repeating the similar logic on the new table. But it's taking so many times in real life it feels like there should be an easier solution.</p>
  
  <p>The table I want is like so:</p>
  
  <pre><code>        ID          Type     Start           End
           1           A       01/01/2017      06/01/2017 
           1           A       09/01/2017      10/01/2017
  </code></pre>