Find the date after a gap in date range in sql

Question

I have these date ranges that represent start and end dates of subscription. There are no overlaps in date ranges.

Start Date   End Date
1/5/2015 -  1/14/2015
1/15/2015 - 1/20/2015
1/24/2015 - 1/28/2015
1/29/2015 - 2/3/2015

I want to identify delays of more than 1 day between any subscription ending and a new one starting. e.g. for the data above, i want the output: 1/24/2015 - 1/28/2015.

How can I do this using a sql query?

Edit : Also there can be multiple gaps in the subscription date ranges but I want the date range after the latest one.

Your logic isnt clear. Please read [**How-to-Ask**](http://stackoverflow.com/help/how-to-ask) And here is a great place to [**START**](http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) — Juan Carlos Oropeza, Feb 10 '16 at 23:15
I think it's pretty clear. The "gap" is supposed to be more than 1 day between `end date` and `start date` of the very next row. — Kamil Gosciminski, Feb 10 '16 at 23:17
it doesn't matter how many gaps, he want's to identify them all. Therefore he needs to iterate through each record and query for `start_date > {{row_end_date + 1 day}}`, then he can deduce his gap. Unfortunately I don't know how to do it directly in sql, I'd have to iterate with an ORM. Most likely you'd need to use a store procedure like this: http://stackoverflow.com/questions/5817395/how-can-i-loop-through-all-rows-of-a-table-mysql — TomDunning, Feb 10 '16 at 23:20
But he says he wants to identify "delays" and then outputs "start date - end date". I give up, sorry. — Sebas, Feb 10 '16 at 23:28

score 0 · Answer 1 · answered Feb 10 '16 at 23:34

You do this using a left join or not exists:

select t.*
from t
where not exists (select 1
                  from t t2
                  where t2.enddate = dateadd(day, -1, t.startdate)
                 );

Note that this will also give you the first record in the sequence . . . which, strictly speaking, matches the conditions. Here is one solution to that problem:

select t.*
from t cross join
     (select min(startdate) as minsd from t) as x
where not exists (select 1
                  from t t2
                  where t2.enddate = dateadd(day, -1, t.startdate)
                 ) and
      t.startdate <> minsd;

You can also approach this with window functions:

select t.*
from (select t.*,
             lag(enddate) over (order by startdate) as prev_enddate,
             min(startdate) over () as min_startdate
      from t
     ) t
where minstartdate <> startdate and
      enddate <> dateadd(day, -1, startdate);

Also note that this logic assumes that the time periods do not overlap. If they do, a clearer problem statement is needed to understand what you are really looking for.

score 0 · Answer 2 · answered Feb 10 '16 at 23:38

You can achieve this using window function LAG() that would get value from previous row in ordered set for later comparison in WHERE clause. Then, in WHERE you just apply your "gapping definition" and discard the first row.

SQL FIDDLE - Test it!

Sample data:

create table dates(start_date date, end_date date);

insert into dates values 
  ('2015-01-05','2015-01-14'),
  ('2015-01-15','2015-01-20'),
  ('2015-01-24','2015-01-28'), -- gap
  ('2015-01-29','2015-02-03'),
  ('2015-02-04','2015-02-07'),
  ('2015-02-09','2015-02-11'); -- gap

Query

SELECT
  start_date,
  end_date
FROM (
  SELECT
    start_date,
    end_date,
    LAG(end_date, 1) OVER (ORDER BY start_date) AS prev_end_date
  FROM dates
  ) foo
WHERE
  start_date IS DISTINCT FROM ( prev_end_date + 1 ) -- compare current row start_date with previous row end_date + 1 day
  AND prev_end_date IS NOT NULL -- discard first row, which has null value in LAG() calculation

I assume that there are no overlaps in your data and that there are unique values for each pair. If that's not the case, you need to clarify this.

Find the date after a gap in date range in sql

2 Answers2