CODEWITHSUNDEEP
sqloracle-databaserangedistinct-values
bourrinemusique
bourrinemusique

Reputation: 11

SQL: finding values not in a range

I have a table like this:

cable_id total_capacity start end
XX1 12 1 5
XX1 12 7 7
XX1 12 8 9
XX1 12 11 12

(This is an example for one cable_id, there are millions more) Using Oracle SQL, I want to create a table for each cable_id containing all the start-end values that are not in the range of the total_capacity.

In my example, 6 and 10 are the start-end missing values from the 1 to 12 range, so the resulting table would be something like that:

cable_id missing_number
XX1 6
XX1 10

I thought about creating another table with cable_id and all the total_capacity range numbers (from 1 to 12) then make an outer join with my first table, but this would probably be very time-consuming (remember there are millions of cables) and Oracle doesn't make it easy to create range of values...

Any help on that would be greatly appreciated :)

Upvotes: 0

Views: 436

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270463

If you don't have overlaps, you can use lead() to get the ranges of missing values:

select end + 1, next_start - 1
from (select t.*, lead(start) over (order by start) as next_start
      from t
     ) t
where next_start > end + 1;

Upvotes: 1

Related Questions