Hi
This problem is very similar to the problem below
https://community.oracle.com/tech/developers/discussion/4489356/reverse-engineering-in-sum-according-to-range-and-rate#latest
I have the following structure and data in the table
CREATE TABLE TMP_TAX_RANGE
(
ID_FX NUMBER(3),
PERC_FX NUMBER,
RANGE_BEGIN NUMBER(19,2),
RANGE_END NUMBER(19,2),
DISCOUNT NUMBER(19,2)
)
Insert into TMP_TAX_RANGE (ID_FX, PERC_FX, RANGE_BEGIN, RANGE_END, DISCOUNT) Values (1, 0, 0, 1903.98, 0);
Insert into TMP_TAX_RANGE (ID_FX, PERC_FX, RANGE_BEGIN, RANGE_END, DISCOUNT) Values (2, 0.075, 1903.99, 2826.65, 142.8);
Insert into TMP_TAX_RANGE (ID_FX, PERC_FX, RANGE_BEGIN, RANGE_END, DISCOUNT) Values (3, 0.15, 2826.66, 3751.05, 354.8);
Insert into TMP_TAX_RANGE (ID_FX, PERC_FX, RANGE_BEGIN, RANGE_END, DISCOUNT) Values (4, 0.225, 3751.06, 4664.68, 636.13);
Insert into TMP_TAX_RANGE (ID_FX, PERC_FX, RANGE_BEGIN, DISCOUNT) Values (5, 0.275, 4664.69, 869.36);
COMMIT;
but this time the table has a range of values and must obey the following rule:
Given a value, it must look for which range it fits, and multiply this value by the rate (PERC_FX) done so
must decrease the value of the DISCOUNT column, an example
5000 is in the range of ID_FX equal to 5 in this case PERC_FX is equal to 0.275 so
5000 * 0.0275 =1375
now the value of the DISCOUNT column must be removed
1375 - 869.36 ==> 505.64
So the net value was
5000 - 505.64 ==> 4494.36
In the same way as before, I would like to know if there is a way to do a reverse query, this is based on the net value
arrive at gross value
Example, suppose I have the value 4494.36 , how could I get the value 5000
thank you in advance
using
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production