# SQL & PL/SQL

## get gross value from net value

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

**Solomon Yakobson**on Jan 20 2022