-- The (query) table below has overlapping dates for the same ID (idx) values.
-- For data analytics and BI reporting, the values have to be sequential to avoid 
-- inaccurate computations or even load failures.  The start date of 06/01/2005 
-- for idx 100 and 10/01/2010 for idx 200 are overlapping.  The query below 
-- validates and identifies the overlapping dates.

WITH q_dates AS ( SELECT  100 idx,
   TO_DATE(20050101,'yyyymmdd') s_date, TO_DATE(20091231,'yyyymmdd') e_date FROM DUAL 
 UNION
 SELECT  100 idx,
   TO_DATE(20050601,'yyyymmdd') s_date, TO_DATE(20091231,'yyyymmdd') e_date FROM DUAL
 UNION
 SELECT  100 idx,
   TO_DATE(20100101,'yyyymmdd') s_date, TO_DATE(20991231,'yyyymmdd') e_date FROM DUAL
 UNION
 SELECT  200 idx,
   TO_DATE(20050601,'yyyymmdd') s_date, TO_DATE(20091231,'yyyymmdd') e_date FROM DUAL
 UNION
 SELECT  200 idx,
   TO_DATE(20100101,'yyyymmdd') s_date, TO_DATE(20121231,'yyyymmdd') e_date FROM DUAL
 UNION
 SELECT  200 idx,
   TO_DATE(20101001,'yyyymmdd') s_date, TO_DATE(20991231,'yyyymmdd') e_date FROM DUAL
 )
SELECT  t1.idx,
      TO_CHAR(t2.s_date,'mm/dd/yyyy') s_date, TO_CHAR(t2.e_date,'mm/dd/yyyy') e_date
FROM  q_dates t1
  INNER JOIN q_dates t2   ON  t1.idx = t2.idx
WHERE  t2.s_date > t1.s_date
AND    t2.s_date < t1.e_date;

=> Above query Output

-- The date overlap can also be identified by computing the date difference using
-- Oracle analytic function LAG().  In this example, the date gap is exactly one 
-- day sequentially.

WITH q_dates AS (SELECT  100 idx,
   TO_DATE(20050101,'yyyymmdd') s_date, TO_DATE(20091231,'yyyymmdd') e_date FROM DUAL 
 UNION
 SELECT  100 idx,
   TO_DATE(20050601,'yyyymmdd') s_date, TO_DATE(20091231,'yyyymmdd') e_date FROM DUAL
 UNION
 SELECT  100 idx,
   TO_DATE(20100101,'yyyymmdd') s_date, TO_DATE(20991231,'yyyymmdd') e_date FROM DUAL
 UNION
 SELECT  200 idx,
   TO_DATE(20050601,'yyyymmdd') s_date, TO_DATE(20091231,'yyyymmdd') e_date FROM DUAL
 UNION
 SELECT  200 idx,
   TO_DATE(20100101,'yyyymmdd') s_date, TO_DATE(20121231,'yyyymmdd') e_date FROM DUAL
 UNION
 SELECT  200 idx,
   TO_DATE(20101001,'yyyymmdd') s_date, TO_DATE(20991231,'yyyymmdd') e_date FROM DUAL
 ),
 q_overlap AS (SELECT idx,   s_date, e_date,
  s_date-(LAG(e_date) OVER(PARTITION BY idx ORDER BY idx, e_date))  date_diff
FROM  q_dates)
SELECT  idx,
  TO_CHAR(s_date,'mm/dd/yyyy') s_date,  TO_CHAR(e_date,'mm/dd/yyyy') e_date
FROM q_overlap
WHERE  date_diff != 1;
 
=> Above (LAG) query Output

-- In this example the gap is greater than 1 day between previous end date and next
-- start date (12/31/2009 and 01/05/2010)

WITH q_dates AS (SELECT  100 idx,
   TO_DATE(20050101,'yyyymmdd') s_date, TO_DATE(20091231,'yyyymmdd') e_date FROM DUAL 
 UNION
 SELECT  100 idx,
   TO_DATE(20050601,'yyyymmdd') s_date, TO_DATE(20091231,'yyyymmdd') e_date FROM DUAL
 UNION
 SELECT  100 idx,
   TO_DATE(20100105,'yyyymmdd') s_date, TO_DATE(20991231,'yyyymmdd') e_date FROM DUAL
 UNION
 SELECT  200 idx,
   TO_DATE(20050601,'yyyymmdd') s_date, TO_DATE(20091231,'yyyymmdd') e_date FROM DUAL
 UNION
 SELECT  200 idx,
   TO_DATE(20100101,'yyyymmdd') s_date, TO_DATE(20121231,'yyyymmdd') e_date FROM DUAL
 UNION
 SELECT  200 idx,
   TO_DATE(20101001,'yyyymmdd') s_date, TO_DATE(20991231,'yyyymmdd') e_date FROM DUAL
 ),
 q_overlap AS (SELECT idx, s_date, e_date,
  s_date-(LAG(e_date) OVER(PARTITION BY idx ORDER BY idx, e_date))  date_diff  
FROM  q_dates)
SELECT  idx,
  TO_CHAR(s_date,'mm/dd/yyyy') s_date,  TO_CHAR(e_date,'mm/dd/yyyy') e_date
FROM q_overlap
WHERE  date_diff < 1;

=> Above (LAG) query Output