-- 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
23427