CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Sabyasachi Mitra
Sabyasachi Mitra

Reputation: 365

Continuous and non continuous date spans in Oracle SQL: Finding the earliest date

I need your help to solve this problem:

Here is my data

id      start_date  end_date    
5567    2008-04-17  2008-04-30
5567    2008-05-02  2008-07-31
5567    2008-08-01  2008-08-31
5567    2008-09-01  2009-12-31

Since there is a lapse between 2008-04-30 and 2008-05-02 the requirement is to display the earliest start date after the lapse.

id      start_date  end_date
5567    2008-05-02  2008-08-31

Here is another set of data:

id      start_date  end_date
5567    2008-04-17  2008-04-30
5567    2008-05-01  2008-07-31
5567    2008-08-01  2008-08-31
5567    2008-09-01  2009-12-31

In this case all the spans are continuous so the the earliest start date should be on the output. the output should be:

id      start_date  end_date
5567    2008-04-17  2008-04-30

Here is the code I have used:

select 
id, min(start_date), contig
from (
  select 
   id, start_date, end_date,
   case 
     when lag(end_date) over (partition by id order by end_date) =       
     start_date-1 or row_number() over (partition by id order by    
     end_date)=1 
        then 'c' else 'l' end contig
    from t2 )
 group by id, contig;

It's working when there is no lapses between the span but giving two records when there is a lapse.

For example, when the spans are continuous my query returns:

ID      MIN(START_DATE  CONTIG
5567    17-APR-08       c

But when the data is not continuous it's showing two records:

ID      MIN(START_DATE  CONTIG
5567    02-MAY-08       l
5567    17-APR-08       c

But in this case I only want the 1st record.

I know there is a PL/SQL solution to this but can I achieve it in only SQL? The database is Oracle 11gR2.

Upvotes: 0

Views: 1908

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270643

I think this will do what you want:

select start_date
from (select t2.start_date
      from t2 left join
           t2 t2p
           on t2.start_date = t2p.end_date + 1
      where t2p.end_date is null
      order by t2.start_date nulls last
     ) t
where rownum = 1;

You can also do this with lag():

select coalesce(min(case when prev_end_date is not null then start_date end),
                min(start_date))
from (select t2.*, lag(t2.end_date) over (order by t2.start_date) as prev_end_date
      from t2
     ) t
where prev_end_date is null or prev_end_date <> start_date - 1;

Your "else" condition is a bit tricky. You have to be careful that you don't get the minimum start date all the time.

Upvotes: 2

Related Questions