SQL Query to validate Start_Date and End_Date for each consecutive records

Assume you have requirement to validate the start date and end date for each record provided and your validation or check should be that every consecutive record should have start_date + 1 from the previous record end date :

Here is the sample data :

EMP_PRICING DATA :

ID     NAME   START_DATE    END_DATE
1.      P1           1-JAN-2019        31-MAR-2019
2.      P1           1-APRIL-2019    30-JUN-2019

Here is sample SQL Query to validate this data in the sql query. Please note this can be modified accordingly to your requirements.


SQL Query.


select * from (select * from EMP_PRICING_DATA order by start_dt) a
where exists (select 1 from EMP_PRICING_DATA b
                      where b.name = a.name
                        and b.start_dt <= a.end_dt
                        and b.start_dt <> a.end_dt + 1
                        and b.end_dt >= a.start_dt
                        and b.rowid < a.rowid
                      );