Select postgres between two values

Navigation
2

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

First, I check the% of this customer's return checks and I look for another pre-set rate that will be entitled to this return rate in another table.

An example of a record in the table that I need to fetch: 

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 even when the customer's% return is between 0% and 2%, I need to get the proposal_tax and discount_tax of ID 1 and if it is between 2.01% and 4.00%, I need to get the values of ID 2.

    
asked by anonymous 26.10.2017 / 13:06

3 answers

2

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 retrieve taxa_proposta and taxa_desconto of a given client 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

    
26.10.2017 / 15:12
-1

I'm assuming you're going to pass the Client Return Percentage value to this select, or if you need to, you can do the Required Joins. 

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

Using BETWEEN, you can see if the percentage entered is in this interval per_min to per_max if it has it will return the line found.

I hope I have helped.

    
27.10.2017 / 03:09
-1

I ended up solving my situation, I found in the English Stack at this link link

I have decided 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.

    
26.10.2017 / 13:22
Calling PHP function in a class Problem Visual Studio For Mac