title: Overlapping time intervals: Select "all busy" periods
body: <p>I am trying to use SQL only to select the common overlapping time intervals from sets of grouped time intervals (or <em>periods</em>, which is probably the proper word).</p>
  
  <p>The real world scenario is a call centre with 3+ <em>positions</em> that can receive telephone calls. Positions are filled by specific service reps and their assignments to positions change over time but that is irrelevant to this problem. We can assume that for a given position it is always filled by someone.</p>
  
  <p>The number of positions changes slowly over time. I am trying to generalize the solution so it can handle an arbitrary number of positions.</p>
  
  <p>The input data is a set of <em>calls</em> that are directed to a position and have a start time and end time. Obviously a given position can not have overlapping calls with itself (assume only one call at a time can be taken) but its calls can overlap in time with one or more calls made to other positions.</p>
  
  <p><strong>The problem is to identify from the call data all the time intervals when ALL positions are on a call and thus the call centre is unable to answer any new incoming calls for that period ("all positions busy").</strong></p>
  
  <p>E.g., for three positions (numbered 1, 2 3)</p>
  
  <pre><code>Call  Position  CallStartTime      CallEndTime
  
  1     1         2014-01-01 14:01   2014-01-01 14:33     &lt;--Comprises all busy intervals 1 and 2
  2     1         2014-01-01 14:45   2014-01-01 14:47  
  3     1         2014-01-01 14:53   2014-01-01 14:57  
  4     2         2014-01-01 13:01   2014-01-01 13:53    
  5     2         2014-01-01 13:55   2014-01-01 14:25     &lt;--comprises all busy interval 1
  6     2         2014-01-01 14:27   2014-01-01 14:29     &lt;--comprises all busy interval 2
  7     2         2014-01-01 14:35   2014-01-01 14:41  
  8     3         2014-01-01 14:21   2014-01-01 15:03     &lt;--comprises all busy intervals 1 and 2
  9     3         2014-01-01 16:01   2014-01-01 16:11
  </code></pre>
  
  <p>For the test data above there are TWO time intervals when all positions are busy (distinct cases of overlapping calls for all positions): 14:21 - 14:25 and 14:27 - 14:29.</p>
  
  <p>So the desired result set would be</p>
  
  <pre><code>AllBusyStartTime  AllBusyEndTime
  2014-01-01 14:21  2014-01-01 14:25
  2014-01-01 14:27  2014-01-01 14:29 
  </code></pre>
  
  <p>You see that one call can have multiple overlaps with other calls (e.g., call position 1 14:01-14:33 overlaps with both call position 2 13:55-14:25 and call position 2 14:27-14:29). </p>
  
  <p>Two time intervals (a, b) overlap when a.StartTime &lt; b.EndTime and a.EndTime >= b.StartTime.</p>
  
  <p>If I can get the set of call time intervals where there is an overlap for all positions, the associated "all busy" time interval consists of the GREATEST (most recent) StartTime from the set and the LEAST (oldest) EndTime.</p>
  
  <p>To get closer to the solution I am looking for a generalizable algorithm to determine when n time intervals are mutually overlapping. For intervals a, b, c selecting a overlaps b and a overlaps c is not restrictive enough. A could overlap b but b might not overlap c and you need all of the intervals to overlap with each other.</p>
  
  <p>I am using SQL Server for my testing. I have tried searching the web but have not found anything that covers exactly this case (lots of discussion on simple cases of two overlapping time intervals). I would share the SQL but I am still trying to figure out the "method", which is needed for that to be illuminating.</p>
  
  <p>Even though I have only SQL-Server for my testing I would like to keep the solution as generic as possible as it will probably not be implemented on SQL Server.</p>