title: SQL Query to show gaps between multiple date ranges
body: <p>Im working on a SSRS / SQL project and trying to write a query to get the gaps between dates and I am completely lost with how to write this.Basically we have a number of devices which can be scheduled for use and I need a report to show when they are not in use.</p>
  
  <p>I have a table with Device ID, EventStart and EventEnd times, I need to run a query to get the times between these events for each device but I am not really sure how to do this.</p>
  
  <p>For example:</p>
  
  <pre><code>Device 1 Event A runs from `01/01/2012 08:00 - 01/01/2012 10:00`
  Device 1 Event B runs from `01/01/2012 18:00 - 01/01/2012 20:00`    
  Device 1 Event C runs from `02/01/2012 18:00 - 02/01/2012 20:00`    
  Device 2 Event A runs from `01/01/2012 08:00 - 01/01/2012 10:00`
  Device 2 Event B runs from `01/01/2012 18:00 - 01/01/2012 20:00`
  </code></pre>
  
  <p>My query should have as its result </p>
  
  <pre><code>`Device 1 01/01/2012 10:00 - 01/01/2012 18:00`
  `Device 1 01/01/2012 20:00 - 02/01/2012 18:00`
  `Device 2 01/01/2012 10:00 - 01/01/2012 18:00`
  </code></pre>
  
  <p>There will be around 4 - 5 devices on average in this table, and maybe 200 - 300 + events.</p>
  
  <p><strong>Updates:</strong></p>
  
  <p>Ok I'll update this to try give a bit more info since I dont seem to have explained this too well (sorry!)</p>
  
  <p>What I am dealing with is a table which has details for Events,  Each event is a booking of a flight simulator, We have a number of flight sims( refered to as devices in the table) and we are trying to generate a SSRS report which we can give to a customer to show the days / times each sim is available. </p>
  
  <p>So I am going to pass in a start / end date parameter and select all availabilities between those dates. The results should then display as something like:</p>
  
  <pre><code>Device   Available_From       Available_To
   1       01/01/2012 10:00    01/01/2012 18:00`
   1       01/01/2012 20:00    02/01/2012 18:00`
   2       01/01/2012 10:00    01/01/2012 18:00`
  </code></pre>
  
  <p>Also Events can sometimes overlap though this is very rare and due to bad data, it doesnt matter about an event on one device overlapping an event on a different device as I need to know availability for each device seperately.</p>