title: How to merge/split multiple spans of dates into one timeline (Oracle 11g)?
body: <p>I've been wrestling with this problem for a few days and now I am turning to the masses for help.</p>
  
  <p>My problem is similar, but not quite the same, as a previous solution on this site:
  <a href="https://stackoverflow.com/questions/5252976/pl-sql-split-separate-a-date-into-new-dates-according-to-black-out-dates">PL/SQL Split, separate a date into new dates according to black out dates!</a>
  This solution is rather boolean (include/exclude) whereas my issue involves some of that as well as merging.</p>
  
  <p>While I like to think I have an intermediate/advanced grasp on SQL+PL/SQL ... Oracle Analytic functions apparently boggle my mind.  I've been trying to read/learn but I'm running out of time.</p>
  
  <p>As I'm not sure of the legality of sharing table names (COTS), line of business and such, I'm going to mimic my issue with a vague-er scenario/context.  Hopefully that will ward off the lawyer spirits.</p>
  
  <p>On to the problem:
  I have a table that houses a customer's activity history.  The customer can come and go and so we may have multiple rows in this table (per customer).</p>
  
  <pre><code>CREATE TABLE activity AS
  SELECT 1 AS cust_id,
         TO_DATE('01-JAN-2010') AS start_dt,
         TO_DATE('31-JUL-2010') AS end_dt,
         'EAST' AS region
  FROM DUAL
  UNION
  SELECT 1 AS cust_id,
         TO_DATE('01-FEB-2011') AS start_dt,
         TO_DATE('31-DEC-2011') AS end_dt,
         'EAST' AS region
  FROM DUAL;
  </code></pre>
  
  <p>I also have a table that houses attribute information by span.  A customer could have multiple attribute types at once and each type multiple times for varying timespans.</p>
  
  <pre><code>CREATE TABLE attrib AS
  SELECT 1 AS cust_id,
         'POWER' AS atb_cd,
         TO_DATE('01-JAN-2009') AS atb_start_dt,
         TO_DATE('31-JAN-2010') AS atb_end_dt,
         'LocalNuke' AS provider,
         1.80 AS per_kwh,
         0 AS per_gal
  FROM DUAL
  UNION
  SELECT 1 AS cust_id,
         'POWER' AS atb_cd,
         TO_DATE('01-MAR-2010') AS atb_start_dt,
         TO_DATE('31-MAR-2010') AS atb_end_dt,
         'CoalGuys' AS provider,
         1.60 AS per_kwh,
         0 AS per_gal
  FROM DUAL
  UNION
  SELECT 1 AS cust_id,
         'POWER' AS atb_cd,
         TO_DATE('01-JUN-2010') AS atb_start_dt,
         TO_DATE('30-SEP-2010') AS atb_end_dt,
         'LocalNuke' AS provider,
         1.70 AS per_kwh,
         0 AS per_gal
  FROM DUAL
  UNION
  SELECT 1 AS cust_id,
         'POWER' AS atb_cd,
         TO_DATE('01-MAR-2011') AS atb_start_dt,
         TO_DATE('31-DEC-9999') AS atb_end_dt,
         'GeoHeat' AS provider,
         1.10 AS per_kwh,
         0 AS per_gal
  FROM DUAL
  UNION
  SELECT 1 AS cust_id,
         'WATER' AS atb_cd,
         TO_DATE('01-MAR-2010') AS atb_start_dt,
         TO_DATE('31-DEC-9999') AS atb_end_dt,
         'GlacialGold' AS provider,
         0 AS per_kwh,
         0.60 AS per_gal
  FROM DUAL;
  </code></pre>
  
  <p>The data oddities are intentional, I tried to make this scenario as real-world as I could  without being related to "the real world".</p>
  
  <p>The result should constrain the spans to the customers' activity with this fictitious company and split out all of the overlapping dates to form a timeline.  Data-elements will need to be merged together for reporting.</p>
  
  <p>Visually:</p>
  
  <pre><code>Cust:
           |----------------------|             |------------------------|
  Power:
  |-------------|    |--|    |-------|               |----------------------&gt;
  Water:
                     |------------------------------------------------------&gt;    
  Expected Result:
           |----|----|--|----|----|             |----|-------------------|
  </code></pre>
  
  <p>The solution should be scalable to include other attributes as well.  At the end, I'd have this denormalized info in a table so I could report a customer's data at any point in time.  For instance, if they had activity, power and water on a particular day; I should be able to export the per_kwh, per_gal and activity data for that day.</p>
  
  <p>Example Output (Tabular):</p>
  
  <pre><code>CUST_ID  FROM_DT      THRU_DT      REGION  POWER_PROVIDER  WATER_PROVIDER  PER_KWH  PER_GAL
  -------  -----------  -----------  ------  --------------  --------------  -------  -------
  1        01-JAN-2010  31-JAN-2010  EAST    LocalNuke                       1.80     0
  1        01-FEB-2010  28-FEB-2010  EAST                                    0        0
  1        01-MAR-2010  31-MAR-2010  EAST    CoalGuys        GlacialGold     1.60     0.60
  1        01-APR-2010  31-MAY-2010  EAST                    GlacialGold     0        0.60
  1        01-JUN-2010  31-JUL-2010  EAST    LocalNuke       GlacialGold     1.70     0.60
  1        01-FEB-2011  28-FEB-2011  EAST                    GlacialGold     0        0.60
  1        01-MAR-2011  31-DEC-2011  EAST    GeoHeat         GlacialGold     1.10     0.60
  </code></pre>
  
  <p>I wrote something about 2 years ago (when the requirement was akin to just Activity/Power) using 2 asynchronous cursors processing slow-by-slow (row-by-row).</p>
  
  <p>While performance is important, the biggest reason why I'm trying to find a straight/bulk sql solution is maintainance.  The if/else cursor nesting of my original solution is already difficult to follow and will be exponentially worse with at least 2 more "attribute" spans to split on.</p>
  
  <p>I would be grateful for any and all help any of you may be able to provide.</p>