CODEWITHSUNDEEP
sqloracle-database
amrita
amrita

Reputation: 133

SQL to find date intervals

I have a table with time periods like (no overlap in time periods):

start_date        end_date
-----------------------------
12-aug-14         12-nov-14
12-jan-15         12-apr-15
12-jun-15         12-aug-15
... 5 more

I'm trying to find the in between time periods - something like:

12-nov-14         12-jan-15
12-apr-15         12-jun-15
...

However, my queries are giving all time period differences like:

12-nov-14         12-jan-15
12-nov-14         12-jun-15

My query was:

select 
    l1.end_date, l2.start_date 
from 
    lease l1, lease l2 
where 
    l1.place_no = 'P1' and l2.place_no = 'P1' 
    and l2.start_date > l1.end_date 
order by 
    l1.end_date asc;

Any ideas?

Thanks!

Upvotes: 0

Views: 66

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

Use lead(). That is what the function is designed for:

select l.*,
       lead(start_date) over (partition by place_no order by start_date) as next_start_date,
       (lead(start_date) over (partition by place_no order by start_date) as next_start_date - end_date) as gap
from lease l
where l1.place_no = 'P1';

There is no need for a join or even for subqueries -- unless you want to eliminate the additional row that has a NULL value because there is no next value.

Upvotes: 2

void
void

Reputation: 7890

sort your table, use rownum and then join them:

WITH CTE AS (
    SELECT 
       START_DATE,
       END_DATE,
       ROWNUM AS RN
FROM ( SELECT START_DATE, END_DATE FROM TABLE_NAME ORDER BY 1,2)
)
SELECT T1.END_DATE, T2.START_DATE
FROM CTE T1 JOIN CTE T2 ON T2.RN=T1.RN+1

Upvotes: 1

Rob Conklin
Rob Conklin

Reputation: 9456

This is kind of tricky. You are currently creating a cartesian, which is close, after you create your cartesian, use a group-by to limit it back down to just the start-rows, and the minimum end-rows:

select
 l1.end_date,
 min(l2.start_date)
from 
    lease l1 
    inner join lease l2 ON
        l1.place_no = l2.place_no and
        l2.start_date >= l1 end_date
where 
    l1.place_no = 'P1'
group by
    l1.start_date
having
    l2.start_date != l1.end_date

Sorry about chaning your join syntax on you, it helps me organize my SQL better in my head.

Upvotes: 0

Related Questions