CODEWITHSUNDEEP
sqldatabaseoracle-databaseplsql
AJ-
AJ-

Reputation: 129

Discontinuous records, missing time gaps.

I have a table Item_X which has primary columns as Item_id,Country,Date_from. Other column not part of PK are Date_To, TypeOfSale. There are there TypeOfSale as 1, 2, 3.

For a particular period of time only on type of sale is valid. For Ex:- enter image description here

Date From is always Date_to + 1.

I have some records in my table which are discontinuous. Example:- enter image description here

Record from 1st Feb to 29th Feb is missing.

I want to find out all such records. First record should not be considered and last record can have To_date as null.

Upvotes: 0

Views: 153

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

Here is one method:

select r.*
from records r
where not exists (select 1 from records r2 where r2.item_id = r.item_id and r2.date_from = r.date_to + 1) and
      exists (select 1 from records r2 where r2.item_id = r.item_id and r2.date_from > r.date_to);

This returns the first record before the gap.

Another method uses lead() and lag():

select r.*
from (select r.*,
             lead(date_from) over (partition by item_id order by date_from) as next_date_from,
             lag(date_to) over (partition by item_id order by date_from) as prev_date_to
      from records r
     ) r
where (date_from <> prev_date_to + 1) or
      (date_to <> next_date_from - 1);

This returns both records.

Upvotes: 1

Related Questions