List in each position the employees who had the most change of charge

Question

List in each position the employees who had the most change of charge

Navigation

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

2

Good evening guys.

I have a problem that I have been trying to solve for some time and I came to ask you here.

It is as follows: I have three important entities for this solution: For ease, I'll just put the "minimum" fields.

Official (cod_func, name, cod_cargo)
Charge (cod_chargo, description)
History (cod_cargo, cod_func, startDate, dataFim)

My system is as follows. Employees always face change of charge. And when that change occurs, jobs are stored in a history table with the job title, employee, and date.

That is, there is a relationship in which an employee has a charge. One job has several employees.

The historical table has two foreign keys.

Now I want to select, for each position, the employees that had the most change of position.

That is, for each position, the employee who has changed the most and is in the position now. Do you understand?

I tried to do it this way, but I do not know how to "reduce it further."

SELECT c.descricao, f.nome, count(*) as qtd 
FROM historicos
JOIN funcionario f on f.cod_func = historicos.cod_func
JOIN cargo c on c.cod_cargo = f.cod_cargo
GROUP BY c.dsc_cargo, f.nome

sql select

asked by anonymous 27.11.2014 / 23:56

2 answers

Null pointer exception on a radio button Best practice of data persistence

score 1 · Answer 1

I believe it works, without a BD to test

Funcionario (cod_func, nome, cod_cargo)
Cargo (cod_cargo, descricao)
Historico (cod_cargo, cod_func, dataInicio, dataFim)

- history of civil servants

select cod_cargo,cod_func,sum(*) qtd_cargos
from   historico
group by cod_cargo,cod_func

- "champions"

select cod_cargo,cod_func
from (select cod_cargo,cod_func,sum(*) qtd_cargos
      from   historico
      group by cod_cargo,cod_func) virtual1
where qtd_cargos = (select max(qtd_cargos)
                    from (select cod_cargo,cod_func,sum(*) qtd_cargos
                          from   historico
                          group by cod_cargo,cod_func) virtual2
                    where virtual2.cod_cargo = virtual1.cod_cargo)

Maybe a solution via "analityc functions" will be cleaner but I believe it will work on every DBMS that accepts virtual tables.

score 0 · Answer 2

This answer holds true for SQL Server. If you need it for some other bank, please indicate in the question.

SELECT c.descricao, f.nome, h.qtd
FROM (
    SELECT cod_func, qtd = count(1) over (partition by cod_func)
    from historicos
    group by cod_func
    order by qtd desc
) h
JOIN funcionario f on f.cod_func = h.cod_func
JOIN cargo c on c.cod_cargo = f.cod_cargo