Odtnhj

after quite some time spent online to try to find an answer to my problem I decided to post it here.

I have a table which contains intervals of time during which some resources are not available. I would like to extract all time interval in which all resources are not available.

With the example below, I would have: start: 2018-11-16 12:30:00, end: 2018-11-16 12:45:00

Start End Resource
------------------- ------------------- ---------- 
2018-11-15 12:00:00 2018-11-15 13:00:00 resource A
2018-11-15 12:00:00 2018-11-15 13:00:00 resource B
2018-11-15 12:30:00 2018-11-15 14:00:00 resource C
2018-11-15 12:00:00 2018-11-15 12:45:00 resource D
2018-11-18 12:00:00 2018-11-18 13:00:00 resource A
2018-11-19 11:40:00 2018-11-19 12:20:00 resource B
2018-11-15 16:00:00 2018-11-15 17:00:00 resource D

Would someone have any idea?

Thanks in advance.

If you know the number of resources (or it can be calculated) and there's no overlapping Start/End range for a resource (i.e. you got clean data):

select Start, "End"
from
 ( -- calculate a Cumulative Sum to get the count of resources unavailable at any point in time
 select Start,
 lead(Start) over (order by Start, x) as "End", 
 sum(x) over (order by Start, x rows unbounded preceding) as cnt
 from
 ( -- combine starts & ends into a single table and assigne +/-1 to each row
 select Start, 1 as x
 from tab
 union all 
 select "End", -1 as x
 from tab
 ) as dt
 ) as dt
where cnt = 4 -- find the row where all resources are unavailable
-- where cnt = (select count(*) from resources)