title: How to consolidate effective_dt and expiration_dt time slices for consecutive records where certain columns remain constant?
body: <p>does anyone have suggestions for this problem? i am trying to use oracle SQL to consolidate spans of <code>effective_dt</code> to <code>expiration_dt</code> where values in <code>col_a</code>, <code>col_b</code>, and <code>col_c</code> remain constant, but only for consecutive records where there is no change in any of the 3 columns in between.</p>
  
  <p>if it helps, it is safe to assume that the effective date of the next record (by employee) is equal to the previous record plus 1 day.</p>
  
  <p>i tried with <code>min()</code>, <code>max()</code> and <code>group by</code>, but the problem is the scenario below will return 12/1-12/31. then i tried with <code>lead()</code> function, but the problem is i do not know in advance how many records i will need to consolidate.</p>
  
  <p>assume that i can get the data into the form below:</p>
  
  <pre><code>+----------+--------------+---------------+---------+---------+---------+
  | employee | effective_dt | expiration_dt |  col_a  |  col_b  |  col_c  |
  +----------+--------------+---------------+---------+---------+---------+
  |     0001 | 12/1/2012    | 12/4/2012     | value_a | value_a | value_a |
  |     0001 | 12/5/2012    | 12/6/2012     | value_a | value_a | value_a |
  |     0001 | 12/7/2012    | 12/10/2012    | value_a | value_a | value_a |
  |     0001 | 12/11/2012   | 12/17/2012    | value_a | value_b | value_a |
  |     0001 | 12/18/2012   | 12/31/2012    | value_a | value_a | value_a |
  +----------+--------------+---------------+---------+---------+---------+    
  </code></pre>
  
  <p>expected result:</p>
  
  <pre><code>+----------+--------------+---------------+---------+---------+---------+
  | employee | effective_dt | expiration_dt |  col_a  |  col_b  |  col_c  |
  +----------+--------------+---------------+---------+---------+---------+
  |     0001 | 12/1/2012    | 12/10/2012    | value_a | value_a | value_a |
  |     0001 | 12/11/2012   | 12/17/2012    | value_a | value_b | value_a |
  |     0001 | 12/18/2012   | 12/31/2012    | value_a | value_a | value_a |
  +----------+--------------+---------------+---------+---------+---------+
  </code></pre>
  
  <p>attempt 1:</p>
  
  <pre><code>SELECT employee,
    MIN(effective_dt),
    MAX(expiration_dt),
    col_a,
    col_b,
    col_c
  FROM
    (SELECT employee, effective_dt, ... FROM table_x, table_y, ... where...
    ) table_a
  GROUP BY employee,
    col_a,
    col_b,
    col_c;
  </code></pre>
  
  <p>attempt 2:</p>
  
  <pre><code>SELECT employee,
    effective_dt,
    lead(expiration_dt, 1) over (partition BY employee, col_a, col_b, col_c order by effective_dt) expiration_dt,
    col_a,
    col_b,
    col_c
  FROM
    (SELECT employee, effective_dt, ... FROM table_x, table_y, ... where...
    ) table_a;
  </code></pre>
  
  <p>thank you!</p>