Oracle SQL - Distinct Continuous Range of Dates

Question

Given a table with columns representing a start and end:

Start        End
01/01/2016   03/01/2016
01/01/2016   06/01/2016

I'm trying to build a query that can recognize that the distinct set of ranges is the following:

[01/01/2016, 03/01/2016)
[03/01/2016, 06/01/2016)

What is this concept called and how can I achieve it with SQL ?

Well, how exactly would you define the set of ranges? If you try to answer this question you might find yourself very close to the answer... — Giorgos Altanis
– Giorgos Altanis, Commented Apr 14, 2017 at 21:13

Gordon Linoff · Accepted Answer · 2017-04-14 21:14:32Z

3

You can bring all the dates together and use lead():

with d as (
      select start as dte
      from t
      union  -- on purpose to remove duplicates
      select end as dte
      from t
     )
select dte as start, lead(dte) over (order by dte) as end
from d;

If you want to prevent the final NULL value . . .

select d.*
from (select dte as start, lead(dte) over (order by dte) as end
      from d
     ) d
where end is not null;

answered Apr 14, 2017 at 21:14

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

jitendra · Accepted Answer · 2017-04-15 02:00:07Z

0

select lag(end_date,1,start_date) over(order by end_date) start_date,end_date from jtest14;

answered Apr 15, 2017 at 2:00

jitendra

113 bronze badges

Collectives™ on Stack Overflow

Oracle SQL - Distinct Continuous Range of Dates

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related