0

I have two numeric ranges [1, 100] and [50, 200] :

1       100
50      200
  • This is an intersection

Another case : with the ranges [40, 100] and [10, 200] 

40      100
10      200
  • This is an inclusion

How can I write an SQL query (or a PL:SQL procedure) that detects the inclusion / intersection.

I tried using minus but it doesn't work in all cases.

EDIT 1

The ranges are passed to the procedure as rows in a table.

devio
  • 1,147
  • 5
  • 15
  • 41
  • how are the ranges passed in? As four parameters? As rows in a table? – Boneist Mar 22 '17 at 09:00
  • rows in a table – devio Mar 22 '17 at 09:19
  • Perhaps you had better update your question to give an accurate representation of your table. For example, are there only ever two rows? Could there be more than two rows? If so, are you wanting to compare rows for given sets (e.g. each id can have multiple ranges and you want to compare those ranges with each other)? – Boneist Mar 22 '17 at 09:26
  • Just two rows. It is clear in my question *given two ranges* as well as my post. – devio Mar 22 '17 at 09:27
  • Not everyone accurately represents their real-life situation, which is why I asked for clarification. – Boneist Mar 22 '17 at 09:47

2 Answers2

1

Here's one way:

WITH sd AS (SELECT start_range,
                   end_range,
                   row_number() OVER (ORDER BY start_range, end_range) rn
            FROM   sample_data)
SELECT sd1.*,
       sd2.*,
       CASE WHEN sd1.start_range >= sd2.start_range AND sd1.end_range <= sd2.end_range THEN 'INCLUSION'
            WHEN sd2.start_range >= sd1.start_range AND sd2.end_range <= sd1.end_range THEN 'INCLUSION'
            WHEN sd1.start_range <= sd2.end_range AND sd1.end_range >= sd2.start_range THEN 'INTERSECTION'
            ELSE 'NONE'
       END range_overlap_type
FROM   sd sd1
       INNER JOIN sd sd2 ON sd1.rn = 1 AND sd2.rn = 2;
Boneist
  • 22,910
  • 1
  • 25
  • 40
0
with source1 as
 (select 60 f, 100 s
    from dual
  union
  select 50 f, 200 s from dual),
t as
 (select source1.*, rownum rn from source1)
select case
         /*(1, 100), (50, 90)*/
         when t1.f <= t2.f and t1.s >= t2.s then
          'Inclusion'
         /*(1, 100), (50, 150)*/
         when t1.f <= t2.f and t1.s < t2.s then
          'Intersection'
       end result
  from (select * from t where rn = 1) t1, (select * from t where rn = 2) t2

I have given the possible two cases as comments

saikumarm
  • 1,565
  • 1
  • 15
  • 30