Relationship with log table

Asked

Viewed 73 times

0

Guys, guys, beauty?

I have two tables from an oracle database:
Fields in Table A:

No. do chamado
Data de conclusão do chamado
Funcionario que concluiu

Fields in Table B:

Funcionario
Area do funcionario
Data da alteracao da area

I need to relate via PL/SQL the two tables so that they result in the table below:
Fields in Table C:

A.No. do chamado
A.Data de conclusão do chamado
A.Funcionario que concluiu
B.Area do funcionario

The problem is that in all the ways that I tried to join the two tables, when the employee went through a change, the relationship has returned all the areas that the employee went through, if had 3 changes, I end up having something like:
No. of the call | Date of completion of the call | Employee who completed | Employee area

######9 | 11/09/2020 | Fulano de tal | Area Z
######9 | 11/09/2020 | Fulano de tal | Area y
######9 | 11/09/2020 | Fulano de tal | Area X
######8 | 22/08/2019 | Fulano de tal | Area Z
######8 | 22/08/2019 | Fulano de tal | Area Y
######8 | 22/08/2019 | Fulano de tal | Area X
######7 | 03/05/2017 | Fulano de tal | Area Z
######7 | 03/05/2017 | Fulano de tal | Area Y
######7 | 03/05/2017 | Fulano de tal | Area X

But what I need is:

######9 | 11/09/2020 | Fulano de tal | Area Z
######8 | 22/08/2019 | Fulano de tal | Area Y
######7 | 03/05/2017 | Fulano de tal | Area X

Could anyone help me? I really couldn’t think of a PL/SQL logic that would return the correct result. Recalling that the date of amendment and the date of completion are not necessarily equal.

Thank you!!!

sql oracle pl-sql

  • And why do you choose Area Z and not Area X or Area Y? You have to define this criterion.

  • What is the criterium ? It is not the biggest date , the 2017 area X in the second satyr came out of the hat by the visa ...

  • @Motta didn’t come out of the hat, it was just one more example of what could come out of the result...

  • @anonimo the criterion is that the person who responded to the call in 2019 worked in area Y, but by internal movement, in 2020 that person went to area Z. But table A, which stores the information of the official who terminated the call, only stores the code of the current area, so that if I just consider this table, all past calls will be associated with the current area

  • I do not know if it is clear enough, but I think the situation is more or less this: If the date of conclusion of the call is prior to a movement, associate to the area prior to the movement. If larger, but smaller than the last movement, to be associated to the area corresponding to the interval. If after the last move, associate the area after last move. An employee may have N moves at the time.

  • These rules do not guarantee uniqueness , has tried to get the most movement ? an example https://answall.com/questions/251198/selectr-registro-recente-ofdeterminada-tabela-postgresql

  • @Motta, are you okay? I have tried yes, in cases of 2 moves solves, but in cases with more than two moves I continue with time intervals associating the employee to the wrong area. I will try this by relating in an external data visualization tool, using more than one query to the database and creating calculated fields in the tool.

Show 2 more comments

1 answer

0

I do not know if I solved it in the best way, but for the cases I tested, it worked using the following consultation:

with
chamadotroca as (
                  select
                        a.No_do_chamado as chamado,
                        a.Data_conclusao_chamado as encerramento,
                        a.funcionario_concluiu as funcionario,
                        max(b.Data_alteracao) as troca
                  from
                        tabelaA a,
                        tabelaB b
                  where
                        a.funcionario_concluiu = b.funcionario
                        and b.Data_alteracao < a.Data_conclusao_chamado 
                  group by a.No_do_chamado ,
                        a.Data_conclusao_chamado,
                        a.funcionario_concluiu,
                  order by 2 desc, 5 desc
                )
select
      ct.chamado as chamado,
      ct.encerramento as encerramento,
      ct.funcionario as funcionario,
      b.area_funcionario AS area,
      ct.troca
from
      chamadotroca ct,
      tabelaB b
where
      ct.funcionario = b.funcionario
      and ct.troca = b.trca_dh_registro

Browser other questions tagged sql oracle pl-sql

You are not signed in. Login or sign up in order to post.