SQL Server - insert data after comparison of 2 tables

Question

I have 2 tables where I want to compare the price of products:

TABLE: PRODUCT

produto  preco
    A     4.5
    B     6 
    C     10

TABLE: RANGE PRICE

produto    valor minimo    valor maximo
A            3.2            6.7
B            2.1            4.5 
C            null           null

I would like to compare the price column with the minimum and maximum values of the Comparison table. If the value is within the range or the comparison data is null, I want to include that value in a third table. If the value is outside the range, I would like to replace it with 0 and also add a flag in the final table. In this example the final table would look like this:

TABLE: FINAL

produto    preco final      flag
A            4.5            0
B            0              1 
C            10             0

Can anyone help me? I already tried to make a Join with parole and I couldn’t, I don’t know if this is the right way

Answer 1

I suggest you carry out the command case ... when to generate the output columns you need, as you can perform the when as an example:

Select * into Final from (
SELECT p.produto, 
  case 
    when ((preco >= valor_minimo) and  (preco <= valor_maximo)) 
      or ((valor_minimo is null) and  (valor_maximo is null)) then preco
    when (preco < valor_minimo or preco > valor_maximo) then 0  
  End as preco_final,
  case 
    when ((preco >= valor_minimo) and  (preco <= valor_maximo)) 
      or ((valor_minimo is null) and  (valor_maximo is null)) then 0
    when (preco < valor_minimo or preco > valor_maximo) then 1  
  End as flag
  FROM PRODUTO p LEFT JOIN range_preco r on p.produto = r.produto
) Consulta_Produto_Em_Range

In the example, we are creating a new table called Final which shall contain the result of the consultation that Consulta_Produto_Em_Range.

If the destination table Final already exists, you can use the following command to include the result of the query in the table Final:

insert into Final 
SELECT p.produto, 
  case 
    when ((preco >= valor_minimo) and  (preco <= valor_maximo)) 
      or ((valor_minimo is null) and  (valor_maximo is null)) then preco
    when (preco < valor_minimo or preco > valor_maximo) then 0  
  End as Preco_Final,
  case 
    when ((preco >= valor_minimo) and  (preco <= valor_maximo)) 
      or ((valor_minimo is null) and  (valor_maximo is null)) then 0
    when (preco < valor_minimo or preco > valor_maximo) then 1  
  End as flag
  FROM PRODUTO p LEFT JOIN range_preco r on p.produto = r.produto

You can use the structure of case ... when to include numerous conditions, should you need.