Ask Tom "SQL Query to find gaps in date ranges"

Ask Tom "SQL Query to find gaps in date ranges"

create table t ( a int, b date, c date );

insert into t values(1, to_date( '01-jan-2007'), to_date( '15-jan-2007'));
insert into t values(2, to_date( '03-jan-2007'), to_date( '10-jan-2007'));
insert into t values(3, to_date( '12-jan-2007'), to_date( '25-jan-2007'));
insert into t values(4, to_date( '20-jan-2007'), to_date( '01-feb-2007'));
insert into t values(5, to_date( '05-feb-2007'), to_date( '10-feb-2007'));
insert into t values(6, to_date( '05-feb-2007'), to_date( '28-feb-2007'));
insert into t values(7, to_date( '10-feb-2007'), to_date( '15-feb-2007'));
insert into t values(8, to_date( '18-feb-2007'), to_date( '23-feb-2007'));
insert into t values(9, to_date( '22-feb-2007'), to_date( '16-mar-2007'));

From the above example, the gap that exists is:

02-feb-2007 --> 04-feb-2007

and we said...

Here is one approach. I used "50" - you can replace 50 with a 'better' value, or you could use

with data as (select max(c-b) c_b from t), r as (select level-1 l from data connect by level <= c_b) select .....

if you do the "data" one, you do not need the case statement as protection...

ops$tkyte%ORA10GR2> variable x number
ops$tkyte%ORA10GR2> exec :x := 50
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2> with
2 r as
3 (select level-1 l
4 from dual
5 connect by level <= :x
6 )
7 select last_dt+1, dt-1
8 from (
9 select dt, lag(dt) over (order by dt) last_dt
10 from (
11 select distinct t.b+r.l dt
12 from t, r
13 where r.l <= t.c-t.b and case when t.c-t.b > :x then 1/0 end is null
14 )
15 )
16 where last_dt <> dt-1
17 /

LAST_DT+1 DT-1
--------- ---------
02-FEB-07 04-FEB-07

writing is gleaned through experience, time, effort and mentoring. Anyone can do it.