Get values between ranges

Question

The more I think of it the more I am confused, could be because it is quite a while that i wrote some complex sql.

I have a table that has a range for a value. Lets call it a range:

RANGE
RANGE_ID   RANGE_SEQ   MIN   MAX  FACTOR
       1           1     0    10       1
       1           2    11   100     1.5
       1           3   101           2.5
       2           1     0    18       1
       2           2    19             2

And I have anothe table that uses these ranges. Lets call it application

APPLICATION
APP_ID   RAW_VALUE  RANGE_ID   FINAL_VALUE
     1         20.0       1          30.0     /*In Range 1, 20 falls between 11 and 100, so 1.5 is applied)*/
     2         25.0       2          50.0
     3         18.5       2          18.5  

I want to get those RAW_VALUES that fall between the ranges. So for range 2, I want those APP_IDs that have a RAW_VALUE between 18 and 19. Similarly for range 1, I want those APP_IDs that have a RAW_VALUE between 10 and 11 and 100 and 101.

I want to know whether this is possible with SQL, and some pointers on what I can try. I don't need the sql itself, just some pointers to the approach.

Answer 1

Try this to get you close

select app_id,raw_value,aa.range_id,raw_value * xx.factor as FinaL_Value
from Application_table  aa
join range_table xx on (aa.raw_value between xx.min and xx.max)
                  and (aa.range_id=xx.range_id)

To get non-matches (i.e. raw_values that do not exist in the table), try this

select app_id,raw_value,aa.range_id
from Application_table  aa
left join range_table xx on (aa.raw_value between xx.min and xx.max)
                  and (aa.range_id=xx.range_id)
where xx.range_id is null

Answer 2

create table tq84_range (
  range_id   number not null,
  range_seq  number not null,
  min_       number not null,
  max_       number,
  factor     number not null,
--
  primary key (range_id, range_seq)
);

insert into tq84_range values (1, 1,  0,  10, 1.0);
insert into tq84_range values (1, 2, 10, 100, 1.5);
insert into tq84_range values (1, 3,101,null, 2.5);

insert into tq84_range values (2, 1,  0,  18, 1.0);
insert into tq84_range values (2, 2, 19,null, 2.0);

create table tq84_application (
  app_id     number not null,
  raw_value  number not null,
  range_id   number not null,
  primary key (app_id)
);

insert into tq84_application values (1, 20.0, 1);
insert into tq84_application values (2, 25.0, 2);
insert into tq84_application values (3, 18.5, 2);

You want to use a left join.

With such a left join, you ensure that each record of the left table (the table appearing prior to left join in the select statement text) will be returned at least once, even though the where condition doesn't find a record in the right table.

If tq84_range.range is null then you know that the join condition didn't find a record in tq84_range, therefore, there seems to be a gap. So you print Missing:.

Since tq84_application.max_ can be null and null appears to indicate infinity or upper bound you test the upper limit with nvl(tq84_range.max_, tq84_application.raw_value

Thus, the select statement will become something like:

select 
       case when tq84_range.range_id is null then 'Missing: ' 
            else                                  '         '
            end,
       tq84_application.raw_value
  from 
       tq84_application       left join
       tq84_range 
    on
       tq84_application.range_id = tq84_range.range_id 
   and
       tq84_application.raw_value between
       tq84_range.min_ and nvl(tq84_range.max_, tq84_application.raw_value);