Oracle query to find out gap in multiple date cycles

Question

As we could see that we have multiple cycles for same customer and contract combination but there is a gap between one cycle end date to another cycle start date.

I would like to find all those customer and contract combinations in my table which has gap in for multiple cycles.Not necessarily the latest cycle has to be ended. It can have an default end date of 01/01/3000 means that cycle is still active.

Can someone please help me with the query ?

Customer_ID   Contract_Id     Start_Date    End_Date
1                 21          01/01/2018   02/01/2018
1                 21          02/06/2018   03/01/2018

Answer 1

You can achieve this by using self-join also.

I have tried to solve it using self-join as following:

-- GAP IN PERIOD
SELECT
    C1.CUSTOMER_ID,
    C1.CONTRACT_ID,
    C1.START_DATE   AS START_DATE,
    C1.END_DATE     AS END_DATE,
    C2.START_DATE - C1.END_DATE AS GAP,
    C2.START_DATE   AS NEXT_START_DATE
FROM
    CONTRACTS C1
    JOIN CONTRACTS C2 ON ( C1.CUSTOMER_ID = C2.CUSTOMER_ID
                           AND C1.CONTRACT_ID = C2.CONTRACT_ID
                           AND C2.START_DATE > C1.START_DATE
                           AND C2.START_DATE - C1.END_DATE <> 1 
                           AND C2.START_DATE - C1.END_DATE > 0) -- ADDED THIS CONDITION

Note: While finding overlapping cycles, I found an issue in the current code and it is corrected -- the last condition

Output:

db<>fiddle demo

Note: I have used the same data from the previous answer.

Cheers!!

--

Updated

Query to find overlapping cycle:

-- OVERLAPPING IN PERIOD

SELECT
    C1.CUSTOMER_ID,
    C1.CONTRACT_ID,
    C1.START_DATE   AS START_DATE,
    C1.END_DATE     AS END_DATE,
    C2.START_DATE - C1.END_DATE AS GAP,
    C2.START_DATE   AS NEXT_START_DATE
FROM
    CONTRACTS C1
    JOIN CONTRACTS C2 ON ( C1.CUSTOMER_ID = C2.CUSTOMER_ID
                           AND C1.CONTRACT_ID = C2.CONTRACT_ID
                           AND C2.START_DATE > C1.START_DATE
                           AND C2.START_DATE - C1.END_DATE <> 1 
                           AND C2.START_DATE - C1.END_DATE < 0)

Output:

updated db<>fiddle demo

Cheers!!

Answer 2

I think you can use LEAD analytic function to get a start date of the next cycle and then compare it to period's end date:

create table contracts (
    Customer_ID     number,
    Contract_Id     number,
    Start_Date      date,
    End_Date        date
);


-- with gap
insert into contracts values (1, 21, to_date('01/01/2018','MM/DD/YYYY') , to_date('02/01/2018','MM/DD/YYYY'));
insert into contracts values (1, 21, to_date('02/06/2018','MM/DD/YYYY'), to_date('03/01/2018','MM/DD/YYYY'));
-- no gap (next start_date = end_date+1)
insert into contracts values (2, 21, to_date('01/01/2018','MM/DD/YYYY') , to_date('02/01/2018','MM/DD/YYYY'));
insert into contracts values (2, 21, to_date('02/02/2018','MM/DD/YYYY'), to_date('03/01/2018','MM/DD/YYYY'));
-- with gap. no end date
insert into contracts values (3, 21, to_date('01/01/2018','MM/DD/YYYY') , to_date('02/01/2018','MM/DD/YYYY'));
insert into contracts values (3, 21, to_date('02/06/2018','MM/DD/YYYY'), null);
-- no gap, no end date
insert into contracts values (4, 21, to_date('01/01/2018','MM/DD/YYYY') , to_date('02/01/2018','MM/DD/YYYY'));
insert into contracts values (4, 21, to_date('02/02/2018','MM/DD/YYYY'), null);
-- one period
insert into contracts values (5, 21, to_date('01/01/2018','MM/DD/YYYY') , to_date('02/01/2018','MM/DD/YYYY'));
-- one period, no end date
insert into contracts values (6, 21, to_date('01/01/2018','MM/DD/YYYY') , null);
commit;

select customer_id, contract_id, start_date, end_date, next_start_date, (next_start_date-end_date) as gap 
from (
    select customer_id, contract_id, start_date, end_date, 
            lead(start_date, 1, end_date+1) over (partition by customer_id, contract_id order by start_date) next_start_date
        from contracts
) 
where next_start_date != end_date + 1;

CUSTOMER_ID CONTRACT_ID START_DAT END_DATE  NEXT_STAR        GAP
----------- ----------- --------- --------- --------- ----------
          1          21 01-JAN-18 01-FEB-18 06-FEB-18          5
          3          21 01-JAN-18 01-FEB-18 06-FEB-18          5