title: Checking for time range overlap, the watchman problem [SQL]
body: <p>I am running into a road block on a larger problem.</p>
  
  <p>As part of a large query I need to solve a "night watchman" problem.
  I have a table with schedule shifts as such:</p>
  
  <pre><code>ID | Start          | End
  1  | 2009-1-1 06:00 | 2009-1-1 14:00
  2  | 2009-1-1 10:00 | 2009-1-1 18:00
  3  | 2009-2-1 20:00 | 2009-2-2 04:00
  4  | 2009-2-2 06:00 | 2009-2-2 14:00
  </code></pre>
  
  <p>As part of a query, I need to determine if there is at least 1 watchman in a room at all times for a given time range.</p>
  
  <p>So if I specified the range <code>2009-1-1 06:00</code> to <code>2009-1-1 12:00</code>, the result is true, because shifts 1 and 2 merge to cover this time period - in fact any number of shifts could be chained to keep the watch up. However if I checked <code>2009-2-1 22:00</code> to <code>2009-1-2 10:00</code>, the result is false because there is a break between 4 and 6am the following morning.</p>
  
  <p>I would like to implement this <em>either</em> in LINQ, or as a user defined function in SQL Server (2005), as in both cases this is just a part of the logic of a larger query that must be run to identify elements that need attention. The real dataset involves about a hundred shift records intersecting any given time period, but not always covering the whole range.</p>
  
  <p>The closest I've found is
  <a href="https://stackoverflow.com/questions/472732/how-to-group-ranged-values-using-sql-server">How to group ranged values using SQL Server</a>
  for number ranges, however it depends on each range ending just before the next range starts. If I could construct the same unified view of the watches, only taking overlapping watches into consideration, then it would be trivial to check if a specific time was covered. A unified view would look like this:</p>
  
  <pre><code>Start          | End
  2009-1-1 06:00 | 2009-1-1 18:00
  2009-2-1 20:00 | 2009-2-2 04:00
  2009-2-2 06:00 | 2009-2-2 14:00
  </code></pre>
  
  <p>Note: This whole thing would be relatively easy to implement by just pulling all the data and running some manual loop on it, however that is the current system, and its rather slow because of the number of shifts and the number of time ranges that must be checked.</p>