title: Oracle queries finding gaps in dates, compare date ranges for gaps
body: <p>How to write queries to find the following data gaps. </p>
  
  <p>--Scenario1 
  -- Query1
  --Find gap within same table (e.g. tableb )</p>
  
  <pre><code>ID      EFF_DT                                  TERM_DT 
  800    6/15/2012 12:00:00.000 AM    10/14/2012 12:00:00.000 AM      -- GAP 
  800    10/16/2012 12:00:00.000 AM    11/14/2012 12:00:00.000 AM
  800    11/15/2012 12:00:00.000 AM    12/31/9999 12:00:00.000 AM
  </code></pre>
  
  <p>--Scenario2 
  -- Query2
  --Find mismatches between 2 tables (tablea  , tableb )</p>
  
  <pre><code>-- tablea  , sample data below 
  ID      EFF_DT                                  TERM_DT 
  400    6/15/2012 12:00:00.000 AM    10/14/2012 12:00:00.000 AM      
  400    11/15/2012 12:00:00.000 AM    12/31/9999 12:00:00.000 AM
  
  500    6/15/2012 12:00:00.000 AM    10/14/2012 12:00:00.000 AM      
  500    10/15/2012 12:00:00.000 AM    11/14/2012 12:00:00.000 AM      
  500    11/15/2012 12:00:00.000 AM    12/31/9999 12:00:00.000 AM
  
  -- tableb, sample data below 
  ID      EFF_DT                                  TERM_DT 
  400    6/15/2012 12:00:00.000 AM    10/13/2012 12:00:00.000 AM      -- mismatch 
  400    11/15/2012 12:00:00.000 AM    12/31/9999 12:00:00.000 AM
  
  500    6/15/2012 12:00:00.000 AM    11/14/2012 12:00:00.000 AM      
  500    11/15/2012 12:00:00.000 AM    12/31/9999 12:00:00.000 AM
  
  
  -- Note 400 is a mismatch since ranges are different in both tables, 500 is okay (even though rows are split they still match )
  -- Need to identify ID 400 
  </code></pre>
  
  <p>The tables sometimes have 1/1/1753 for low date and 12/31/9999 for high date.</p>
  
  <p>Tried the following query but it throws oracle errors for first scenario.
  [ ORA-01841: (full) year must be between -4713 and +9999, and not be 0 ]</p>
  
  <pre><code>SELECT ID, TERM_DT AS gap_lower_bound, next_date AS gap_upper_bound
    FROM (SELECT t.*,
                 LEAD (EFF_DT)
                    OVER (PARTITION BY ID ORDER BY EFF_DT)
                    next_date
            FROM TABLEB t)
   WHERE                                      -- trim(next_date) is not null and
        next_date &gt; TERM_DT + 1
  </code></pre>
  
  <p>Thank you.</p>