oracle - In SQL, how calculate a value based on a range

Question

I have two tables and need to calculate a new value, as I show below

This table contains information about items and the price

ID | Item_Desc | Price
-----------------------
1  | Item 1    |   3
2  | Item 2    |  10
3  | Item 3    |  15

This table contains information about prices ranges and adjust, this values can change in the future

Initial_value | End_value | Adjust
-----------------------------------
        0     |     5     |   5
      5.01    |     10    |   10
     10.01    |     20    |   15

Result: I expect as result is a new price based on the second table:

ID | Item_Desc | Price   | New price
-------------------------------------
1  | Item 1    |   3     |    3.15
2  | Item 2    |  10     |    11
3  | Item 3    |  15     |    17.25

Any idea about a query, that is not hardcoded the range values and the adjust?

question from:https://stackoverflow.com/questions/65837180/in-sql-how-calculate-a-value-based-on-a-range

Answer 1

You can use join and multiplication:

select i.*, i.price * (1 + p.adjust / 100.0) as new_price
from items i left join
     prices p
     on i.price between p.initial_value and p.end_value