title: multithreading - Query to show total time elapsed working on a set of processes
body: <p>I'm trying to determine the total elapsed time to complete a set of processes for a multithreaded application which keeps track of start and end times in a table. The easiest way to describe my problem is with an example.</p>
  
  <p>Here's a dumbed-down version of the table (we'll call it <code>processes</code>) I'm working with:</p>
  
  <pre><code>| id  | start_date          | end_date            |
  ---------------------------------------------------
  |  1  | 07/15/2011 12:00:00 | 07/15/2011 12:01:00 |
  |  2  | 07/15/2011 12:00:00 | 07/15/2011 12:02:00 |
  |  3  | 07/15/2011 12:00:00 | 07/15/2011 12:03:00 |
  |  4  | 07/15/2011 12:01:00 | 07/15/2011 12:05:00 |
  |  5  | 07/15/2011 12:01:00 | 07/15/2011 12:03:00 |
  |  6  | 07/15/2011 12:03:00 | 07/15/2011 12:04:00 |
  |  7  | 07/15/2011 12:03:00 | 07/15/2011 12:07:00 |
  |  8  | 07/15/2011 12:03:00 | 07/15/2011 12:06:00 |
  |  9  | 07/15/2011 12:04:00 | 07/15/2011 12:05:00 |
  | 10  | 07/15/2011 12:05:00 | 07/15/2011 12:07:00 |
  | 11  | 07/15/2011 12:08:00 | 07/15/2011 12:09:00 |
  </code></pre>
  
  <p>With such a small sample of data it's easy enough to visualize this (I'm assuming a thread can finish with a process and instantaneously pick up the next one with no overhead for the purposes of this question)</p>
  
  <pre><code>12:XX: | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 |
  Thread1: 1---4---------------10------]   11--]
  Thread2: 2-------]   6---9---]
  Thread3: 3-----------7---------------]
  Thread4:     5-------8-----------]
  </code></pre>
  
  <p>And from there you can easily tell that the total time spent working on the 11 processes was <strong>8</strong> minutes.</p>
  
  <p>The problem arises because I am dealing with thousands of records, and there are some periods of time where no processing is happening at all.</p>
  
  <p><strong>How can I get this result using a PL/SQL query selecting from the table?</strong></p>