AJ- AJ- - 4 months ago 10
SQL Question

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.

Answer

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.

Comments