Finding gaps in dates

Question

Below is my table with data:

CREATE TABLE customer_wer (
  id_customer NUMBER,
  name VARCHAR2(10),
  surname VARCHAR2(20),
  date_from DATE,
  date_to DATE);

--KAROLINA BIELAWSKA - GAP MAR APR 2000 and JUN JUL 2001

INSERT INTO customer_wer VALUES (2, 'Karolina', 'Bielawska', '01-JAN-00', '28-MAR-00');
INSERT INTO customer_wer VALUES (2, 'Karolina', 'Bielawska', '01-APR-00', '30-JUN-00');
INSERT INTO customer_wer VALUES (2, 'Karolina', 'Bielawska', '01-JUL-00', '30-SEP-00');
INSERT INTO customer_wer VALUES (2, 'Karolina', 'Bielawska', '01-OCT-00', '31-DEC-00');
INSERT INTO customer_wer VALUES (2, 'Karolina', 'Bielawska', '01-JAN-01', '31-MAR-01');
INSERT INTO customer_wer VALUES (2, 'Karolina', 'Bielawska', '01-APR-01', '15-JUN-01');
INSERT INTO customer_wer VALUES (2, 'Karolina', 'Bielawska', '01-JUL-01', '30-SEP-01');
INSERT INTO customer_wer VALUES (2, 'Karolina', 'Bielawska', '01-OCT-01', '31-DEC-01');

--LUKASZ JAGIELLO - GAP JUN JUL 2000

INSERT INTO customer_wer VALUES (3, 'Lukasz', 'Jagiello', '01-JAN-00', '31-MAR-00');
INSERT INTO customer_wer VALUES (3, 'Lukasz', 'Jagiello', '01-APR-00', '15-JUN-00');
INSERT INTO customer_wer VALUES (3, 'Lukasz', 'Jagiello', '01-JUL-00', '30-SEP-00');
INSERT INTO customer_wer VALUES (3, 'Lukasz', 'Jagiello', '01-OCT-00', '31-DEC-00');
INSERT INTO customer_wer VALUES (3, 'Lukasz', 'Jagiello', '01-JAN-01', '31-MAR-01');
INSERT INTO customer_wer VALUES (3, 'Lukasz', 'Jagiello', '01-APR-01', '30-JUN-01');
INSERT INTO customer_wer VALUES (3, 'Lukasz', 'Jagiello', '01-JUL-01', '30-SEP-01');
INSERT INTO customer_wer VALUES (3, 'Lukasz', 'Jagiello', '01-OCT-01', '31-DEC-01');

I have found gaps with below select:

SELECT date_from, date_to, id_customer, next_date
  FROM 
    (SELECT k.*,
          LEAD(date_from, 1) OVER (partition by id_customer order by date_from) AS next_date
      FROM customer_wer k
    )
WHERE date_to + 1 <> next_date AND date_to < '31-DEC-01' AND date_to < next_date;

I would like to know if it is possible to have same results with duplicating table. I cannot find correct solution. My select is below:

SELECT COUNT(k.id_customer)
  FROM customer_wer k
  JOIN customer_wer w
  ON k.id_customer = w.id_customer
  WHERE (w.date_from < k.date_from AND w.date_to + 1 < k.date_from);

There should be one more limitation.

Answer 1

This self-join query produced the same output as yours:

with data as (
  select rownum rn, x.* from customer_wer x 
    order by id_customer, date_from)
select k.date_from, k.date_to, k.id_customer, w.date_from next_date
  from data k 
  join data w on k.id_customer = w.id_customer and k.rn + 1 = w.rn
  where k.date_to+1 < w.date_from
  order by k.id_customer, k.date_from;

And I think that you should change order in your analytical function to:

OVER (partition by id_customer order by date_from)