Tuesday, October 17, 2006

Find Gaps In Date Ranges

A couple of months ago, I have posted an article about finding unused key ranges. Today, I faced a very similar problem: find gaps in time ranges.

I have adapted the sql of the previous post to work with date ranges. To avoid problems with the maximum date 9999-12-31, I do convert the dates to a number (the number of the day in the Julian calendar).

Here is what I came up with:
-- drop test table
drop table timeline;

-- create test table
create table timeline (id number, valid_from date, valid_until date);

-- populate test table
-- - leave two gaps in the time dimension
insert into timeline values
(1, to_date('2004-01-01', 'YYYY-MM-DD'), to_date('2004-07-30', 'YYYY-MM-DD'));
--insert into timeline values
--(2, 0, to_date('2004-07-31', 'YYYY-MM-DD'), to_date('2004-07-31', 'YYYY-MM-DD'));
insert into timeline values
(3, to_date('2004-08-01', 'YYYY-MM-DD'), to_date('2005-10-31', 'YYYY-MM-DD'));
--insert into timeline values
--(4, 0, to_date('2005-11-01', 'YYYY-MM-DD'), to_date('2006-12-31', 'YYYY-MM-DD'));
insert into timeline values
(5, to_date('2007-01-01', 'YYYY-MM-DD'), to_date('9999-12-31', 'YYYY-MM-DD'));

-- select the gaps
select to_date(decode(prev_end_dt
,0 ,1721424 -- 0001-01-01 (the minimum date)
,'J') gap_start_dt
, to_date(next_start_dt-1,'J') gap_end_dt
from (
select lag(to_number(to_char(valid_until,'J')), 1, 0)
over (order by valid_until) prev_end_dt
, to_number(to_char(valid_from,'J')) next_start_dt
from timeline
union all
select nvl(max(to_number(to_char(valid_until,'J'))),0)
, 5373484 -- 9999-12-31 (the maximum date)
from timeline
where prev_end_dt+1 < next_start_dt

No comments: