SQL finding gaps between overlapping date ranges

user_19640813 0
Viewed 52

I have these below table with ID, start_dt and end_dt and I need to find if there is a gap between date ranges. You can think of start_dt and end_dt as insurance_start_dt and insurance_end_dt and I need to find if ever there is an insurance gap. The start_dt and end_dt could overlap with other dates.

ID start_dt  end_dt
1|2016-07-01|2020-05-01
1|2017-04-12|2020-04-12
1|2008-10-21|2017-10-18
1|2016-07-15|2016-11-21
1|2013-03-04|2013-06-08

The answer of the above should be NULL since there is no insurance gap.

Example:2

ID start_dt  end_dt
1|2017-04-12|2020-04-12
1|2014-10-21|2016-11-21
1|2013-07-15|2015-05-21
1|2013-03-04|2013-06-08

Ans:-

2013-06-08|2013-07-15
2016-11-21|2017-04-12

I have tried calendar gaps and island approach but it seems nothing is working out. How could I solve this?

impala date-range date mysql sql
1 Answers

Work in progress and I don't have a MYSQL database to use, but most of these functions from postgres should translate. You may need to adjust to MYSQL.

I'm creating many tables to break this down. You can of course condense as needed. Also, I'm sure there are better ways to accomplish this but perhaps this is a bandaid until you can find another way.

Create your policy table:

create table my_table (
  policy_id integer, 
  start_date date, 
  end_date date
  );

insert into my_table values 
(1, '2016-07-01', '2020-05-01'),
(1, '2017-04-12', '2020-04-12'),
(1, '2008-10-21', '2017-10-18'),
(1, '2016-07-15', '2016-11-21'),
(1, '2013-03-04', '2013-06-08'),
(2, '2017-04-12', '2020-04-12'),
(2, '2014-10-21', '2016-11-21'),
(2, '2013-07-15', '2015-05-21'),
(2, '2013-03-04', '2013-06-08');

Next, create a calendar table based on the min/max dates of your policy table.

create table my_calendar as (
    with min_max_calendar_dates as (
    select min(start_date) as min_date, max(end_date) as max_date
    from my_table
    )
    select date(generate_series(min_date, max_date, '1 day')) as cal_date
    from min_max_calendar_dates
);

Next, generate the dates from your policy ranges.

create table my_policy_dates as (
    select policy_id, date(generate_series(start_date, end_date, '1 day')) as date
    from my_table
);

Next, creating a table with all the dates between min/max date by policy id. This pretends the policies were never closed.

create table my_policy_calendar as (
    with pol_min_max as (
    select policy_id, min(start_date) as min_date, max(end_date) as max_date
    from my_table
    group by policy_id
    )
    select t.policy_id, c.cal_date
    from my_table t, my_calendar c, pol_min_max mm 
    where t.policy_id = mm.policy_id
    and c.cal_date >= mm.min_date
    and c.cal_date <= mm.max_date
    group by 1,2
)

Next, create a table which identifies the missing dates for each policy.

create table my_policy_gaps as (
    select policy_id, cal_date
    from my_policy_calendar
    where policy_id || cal_date not in (select policy_id || date from my_policy_dates)
);

So now you have the dates. The next step is formatting to your liking, such as showing in a format you displayed in the question. I'm pondering that piece.

-----------EDIT------------

Final query, showing your gaps by policy:

with gap_start as (
select policy_id, cal_date as gap_start, 
  rank() over (partition by policy_id order by cal_date) as gap_rank
from my_policy_gaps
where policy_id || date(cal_date - interval '1 day') not in (select policy_id || cal_date from  my_policy_gaps)
), 
gap_end as (
select policy_id, cal_date as gap_end, 
  rank() over (partition by policy_id order by cal_date) as gap_rank
from my_policy_gaps
where policy_id || date(cal_date + interval '1 day') not in (select policy_id || cal_date from  my_policy_gaps)
)
select s.policy_id, s.gap_start, e.gap_end
from gap_start s
join gap_end e
  on s.policy_id = e.policy_id
 and s.gap_rank = e.gap_rank
order by policy_id, gap_start

Output:

policy_id  gap_start    gap_end
2          2013-06-09   2013-07-14
2          2016-11-22   2017-04-11
user_131180090
Related