title: SQL query challenge
body: <p>So here's yet another 'write a query to X' challenge.</p>
  
  <p>I'm monitoring a number of networked vending machines.  Each machine has a number of parts, e.g. bank note acceptor, coin system, printer and so on.</p>
  
  <p>Problems with machine parts are logged in table, let's call it 'faults', which looks something like this (irrelevant fields omitted):</p>
  
  <pre><code>machineid           partid         start_time            end_time
  ---------           ------         ----------------      ----------------
         1                2          2009-10-05 09:00      NULL
         1                3          2009-10-05 08:00      2009-10-05 10:00
         2                2          2009-09-30 12:00      2009-09-30 14:00
         3                4          2009-09-28 13:00      2009-09-28 15:00
         3                2          2009-09-28 12:00      2009-09-28 14:00
  </code></pre>
  
  <p>end_date is NULL if the problem is currently ongoing.</p>
  
  <p>I need a query which show time periods for which the machine as a whole is down, and which can account for overlapping ranges, collapsing them down into a single record.  So for the sample data above, it would produce:</p>
  
  <pre><code>machineid          start_time            end_time
  ---------          ----------------      ----------------
         1           2009-10-05 08:00      NULL
         2           2009-09-30 12:00      2009-09-30 14:00
         3           2009-09-28 12:00      2009-09-28 15:00
  </code></pre>
  
  <p>It's not tough to write procedural code to do this line by line, but a nice declarative SQL query would be more useful, more elegant.  It seems like it ought to be possible, I just can't quite get there though.</p>
  
  <p>SQL dialect is Oracle.  Analytic functions are availabe if that would help.</p>
  
  <p>Thanks!</p>