join two tables with date condition [closed]

Question

join two tables with date condition [closed]

Navigation

#1 by (0 votes)
#2 by (0 votes)

1

I need to join two tables, the first one presenting all the records contained in it, with the condition that what is in the second table has a lower price with data_ini and dt_fim using current_date to define if it is inside of the condition that is, within the current date.

Example:

Table 1:

---------------+------------+------
cod_barras     | descrição  | preco
---------------+------------+------
00000000000017 | PAO DE SAL |  0,50
00000000000122 | PEPSI 1 5L |  5,00
00000000000987 | TAIOBA     |  2,00
00000000001526 | ALHO KG    | 20,00

Table 2:

---------------+-------+------------+-----------
barras         | preco | DT_ini     | DT_FIM
---------------+-------+------------+-----------
00000000000017 |  0,30 | 03/11/2017 | 10/11/2017
00000000001526 | 15,00 | 05/11/2017 | 08/11/2017

Considering that current_date = 04/11/2017, should look like this:

---------------+------------+------
cod_barras     | descrição  | preco
---------------+------------+------
00000000000017 | PAO DE SAL |  0,30
00000000000122 | PEPSI 1 5L |  5,00
00000000000987 | TAIOBA     |  2,00
00000000001526 | ALHO KG    | 20,00

Considering that current_date = 07/11/2017, should look like this:

---------------+------------+------
cod_barras     | descrição  | preco
---------------+------------+------
00000000000017 | PAO DE SAL |  0,30
00000000000122 | PEPSI 1 5L |  5,00
00000000000987 | TAIOBA     |  2,00
00000000001526 | ALHO KG    | 15,00

Considering that current_date = 11/11/2017, should look like this:

---------------+------------+------
cod_barras     | descrição  | preco
---------------+------------+------
00000000000017 | PAO DE SAL |  0,50
00000000000122 | PEPSI 1 5L |  5,00
00000000000987 | TAIOBA     |  2,00
00000000001526 | ALHO KG    | 20,00

That is, I need an item that exists in Table 2 within a specific date to pick up the price from Table 2 and not from Table 1.

It will take the value of table 1, if it is not contained in table 2 and within the current date.

Who can help, is a bit difficult.

sql data

asked by anonymous 03.11.2017 / 17:03

2 answers

Concurrent programming Clustering in R

score 0 · Answer 1

When you join, it does not do it right in the table filters before, so it gets easier;

for example

 select*from produto a
 join (select cod_barras, min(valor) valor
           from produtos_preco  
              where data_final>data_atual 
                and data_inicia<data_atual
                group by cod_barras) b on a.cod_barras =b.cod_barras

Such a thing, from what I have understood; but I found it a bit difficult to understand the question:)

score 0 · Answer 2

Make a left outer join by putting the conditions in the join, and use the coalesce for the products that do not have matching records in table2:

Select
p.cod_barras,
p.descricao,
coalesce(t.preco,p.preco) as preco
from tabela1 p 
left outer join tabela2 t
                on p.cod_barras = t.barras 
                and t.dt_ini <= current_date 
                and t.dt_fim >= current_date
                and t.preco < p.preco --(Só vai buscar na segunda tabela se o preço for menor certo ? caso contrário, só tirar essa linha)

I put it in SQLFiddle: link