Select postgres between two values

Question

I am developing a system and I need to perform a select to fetch the % rate a customer will have.

First I check what % of this customer’s cheque refund and look in another table what the predefined rate he will be entitled from this % refund.

An example of a record in the table I need to search for:

id | descricao        | per_min | per_max | taxa_proposta | taxa_desconto
1  | De 0,00% a 2,00% | 0.00    | 2.00    | 100.00        | 0.75 
2  | De 2,01% a 4,00% | 2.01    | 4.00    | 73.40         | 0.55

Or if the % of the customer’s return is between 0% and 2% need to take the proposed tax and taxe_discount ID 1 and if it is between 2,01% to 4,00% need to take the values of ID 2.

Answer 1

Assuming you have a structure like this:

CREATE TABLE (
    id BIGINT,
    id_cliente BIGINT,
    taxa_devolucao REAL
);

INSERT INTO tb_devolucao ( id, id_cliente, taxa_devolucao ) VALUES ( 1, 100, 1.25 );
INSERT INTO tb_devolucao ( id, id_cliente, taxa_devolucao ) VALUES ( 2, 200, 3.33 );

CREATE TABLE tb_foobar
(
    id BIGINT,
    descricao TEXT,
    per_min REAL,
    per_max REAL,
    taxa_proposta REAL,
    taxa_desconto REAL
);

INSERT INTO tb_foobar ( id, descricao, per_min, per_max, taxa_proposta, taxa_desconto ) VALUES ( 1, 'De 0,00% a 2,00%', 0.00, 2.00, 100.0, 0.75 );
INSERT INTO tb_foobar ( id, descricao, per_min, per_max, taxa_proposta, taxa_desconto ) VALUES ( 2, 'De 2,01% a 4,00%', 2.01, 4.00, 73.40, 0.55 );

You can recover the taxa_proposta and the taxa_desconto of a given customer from its taxa_devolucao as follows:

SELECT
    fb.*
FROM
    tb_foobar AS fb
JOIN
    tb_devolucao AS dev ON ( dev.taxa_devolucao BETWEEN fb.per_min AND fb.per_max )
WHERE
    dev.id_cliente = 200;

Test in Sqlfiddle

Answer 2

I ended up solving my situation, I found in the English Stack at this link https://stackoverflow.com/questions/38467298/how-to-correctly-use-fetch-first-in-postgresql

I resolved it this way:

SELECT 
    * 
FROM 
    nome_tabela
WHERE
    per_max >= 2.01
FETCH FIRST 1 ROWS ONLY;

This query returns the values of ID 2 which would be correct for this case.

Answer 3

I’m taking into consideration that you will pass the value of Percentage of customer return for this select, or if you need to, you can do oj Necessary Joins.

SELECT id,taxa_proposta,taxa_desconto 
      FROM taxa_pre_definida 
          WHERE __Percentual de devolução do cliente__ 
               BETWEEN per_min AND per_max

With the use of BETWEEN, you can see if the percentage reported is in this range per_min up to per_max if you have it will return the line found.

I hope I’ve helped.