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
that fall between the ranges. So for range 2, I want those
s that have a
between 18 and 19. Similarly for range 1, I want those
s that have a
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.
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