title: How to merge consecutive date range Oracle
body: <p>I am facing a problem. I can't figure out how to merge consecutive date range rows together, based on two dimensions. One is OK for me, but second makes troubles</p>
  
  <p>Let's imagine table in this structure with four possible scenarios</p>
  
  <pre><code>  emp_id  |  level  |  date_from   |   date_to    
  --------------------------------------------------
      1     |   A     |  7/31/2015   |  3/31/2016
      1     |   A     |  4/1/2016    |  1/1/3000
  
      2     |   A     |  7/31/2015   |  1/1/3000
  
      3     |   A     |  5/31/2015   |  12/31/2015
      3     |   B     |  1/1/2016    |  3/31/2016
      3     |   A     |  4/1/2016    |  6/30/2016
      3     |   B     |  7/1/2016    |  1/1/3000
  
      4     |   A     |  5/31/2015   |  12/31/2015
      4     |   A     |  1/1/2016    |  6/30/2016
      4     |   B     |  7/1/2016    |  1/1/3000
  </code></pre>
  
  <p>I want to merge only those rows, that have consecutive date ranges <strong>and act_level = prev_level</strong> </p>
  
  <p>I tried to do something like this</p>
  
  <pre><code>SELECT emp_id
  , level
  , date_from
  , date_to
  --
  , CASE
      WHEN lag(level) over (partition by emp_id order by date_from) = level THEN 
           CASE
               WHEN lag(date_to) over (partition by emp_id, level order by date_from) = date_from-1 
                 THEN lag(date_from) over (partition by code_employee, level_name order by date_from)
               ELSE NULL
           END
      ELSE 
           CASE
               WHEN lag(level) over (partition by emp_id order by date_from) = level
                       OR
                    lead(level) over (partition by emp_id order by date_from) = level
                  THEN NULL
               ELSE date_from
           END
    END date_from_new
  , date_to as date_to_new
  --
  FROM src_table
  --
  WHERE 1=1
  </code></pre>
  
  <p>this gives me nearly the results that I want:</p>
  
  <pre><code>  emp_id  |  level  |  date_from   |   date_to   |  d_from_new | d_from_to 
  --------------------------------------------------------------------------
      1     |   A     |  7/31/2015   |  3/31/2016  |           | 3/31/2016
      1     |   A     |  4/1/2016    |  1/1/3000   | 7/31/2015 | 1/1/3000
  
      2     |   A     |  7/31/2015   |  1/1/3000   | 7/31/2015 | 1/1/3000
  
      3     |   A     |  5/31/2015   |  12/31/2015 | 5/31/2015 | 12/31/2015
      3     |   B     |  1/1/2016    |  3/31/2016  |  1/1/2016 | 3/31/2016
      3     |   A     |  4/1/2016    |  6/30/2016  |  4/1/2016 | 6/30/2016  
      3     |   B     |  7/1/2016    |  1/1/3000   |  7/1/2016 | 1/1/3000 
  
      4     |   A     |  5/31/2015   |  12/31/2015 |           | 12/31/2015
      4     |   A     |  1/1/2016    |  6/30/2016  | 5/31/2015 | 6/30/2016
      4     |   B     |  7/1/2016    |  1/1/3000   | 7/1/2016  | 1/1/3000
  </code></pre>
  
  <p>I will just filter the result for d_from_new (date_from_new) not null values. But I am not sure what's gonna happen if there will be for example 3x the same level with consecutive date range, or 8times.</p>
  
  <p>And honestly - I don't like the query :) </p>
  
  <p>Do you have any "perfomence-friendly" and "eye-friendly" solution? </p>