title: How do I report gaps with a SQL interval table?
body: <p>I have the following table with non-overlapping intervals and I would like to report all the possible gaps?</p>
  
  <p>The table that I have:</p>
  
  <pre><code>Id         StartDateTime        EndDateTime
  
  3004204 | 2014-01-01 12:47 | 2014-01-01 12:59
  3004204 | 2014-01-01 13:00 | 2014-01-01 14:29
  3004204 | 2014-01-01 14:30 | 2014-01-01 14:41
  3004204 | 2014-01-02 18:01 | 2014-01-02 18:14
  3004204 | 2014-01-02 18:15 | 2014-01-02 18:29
  3004204 | 2014-01-02 18:30 | 2014-01-02 18:44
  3004204 | 2014-01-02 18:45 | 2014-01-02 18:59
  3004204 | 2014-01-02 19:00 | 2014-01-02 19:29
  </code></pre>
  
  <p>And what I would like is per day gap reporting per Id. So in this case it would be:</p>
  
  <pre><code>Id          StartDateTime       EndDateTime
  
  3004204 | 2014-01-01 12:59 | 2014-01-01 13:00
  3004204 | 2014-01-01 14:29 | 2014-01-01 14:30
  3004204 | 2014-01-02 18:14 | 2014-01-02 18:15
  ...
  </code></pre>
  
  <p>Note: the gaps can be of arbitrary length from 1 second to 23:59 hours (currently only displaying per seconds). I think it needs to be done using lags, but that seems to rather inefficient.</p>
  
  <p>Any advice whether this is even possible in SQL Server 2014 in a more efficient way than using lags and leads?</p>
  
  <p>Thanks</p>