CODEWITHSUNDEEP
sqloracle-database
JHarnach
JHarnach

Reputation: 4034

Oracle SQL - Distinct Continuous Range of Dates

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 ?

Upvotes: 0

Views: 108

Answers (2)

jitendra
jitendra

Reputation: 11

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

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270653

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;

Upvotes: 3

Related Questions