Doubt with a query (SQL Server)

Navigation
0

Good afternoon!

I have a set of information in a SQL Server database (I'm still learning SQL Server), and I need to filter the information that is returned to me. They are information of employees of a company (enrollment, cpf, name, position, situation, etc). But I also have 2 fields called year and month, referring to the year and month that that employee worked. I would like to receive only the last month of the last year that employee worked, not every month. For example, there is an employee who worked until the month 06 of 2018 until the month 10 of 2018, and another one that worked from the month 02 of 2018 until the month 12 of 2018. Being that for each month work the situation of him can be different (in one month it can be Active, another month of Vacations, another Dismissed, etc.)

I have tried to use GROUP BY and DISTINCT, but the way I used it did not work. The image below illustrates a bit of my doubt.

Thanksinadvance.

Edit:ThequeryI'mcurrentlyusingisthis(IadaptedfromanexampleIfoundonasite,butitdidnothelpmesolvetheproblem):

SELECTTOP200tabela1.ano,tabela1.mes,tabela1.matricula,tabela1.cpf,tabela1.nome_servidor,tabela1.cargo_ocupado,tabela1.funcao,tabela1.tipo_vinculo,tabela1.data_exercicio,tabela1.data_demissao,tabela1.situacao_funcional,tabela1.enquadramento_salarial,tabela1.orgao_lotacao,tabela1.carga_horaria,tabela1.numero_concursoFROMdbo.vw_portal_rhf_servidorestabela1LEFTJOINdbo.vw_portal_rhf_servidorestabela2ONtabela1.matricula=tabela2.matriculaANDtabela1.ano<tabela2.anoANDtabela1.mes<tabela2.mes

IdonotownthetabletemplatebecauseIonlygetoneviewfromanotherplace.

TheresultI'dliketoreceivewouldbetheoneintheimagebelow,circledinyellow(Iknowitgotweird,butIcannotfigureitoutbetter):

Edit2: (resolution)

I found a resolution, it was the following code: 

SELECT
  tabela1.ano,
  tabela1.mes,
  tabela1.matricula,
  tabela1.cpf,
  tabela1.nome_servidor,
  tabela1.cargo_ocupado,
  tabela1.funcao,
  tabela1.tipo_vinculo,
  tabela1.data_exercicio,
  tabela1.data_demissao,
  tabela1.situacao_funcional,
  tabela1.enquadramento_salarial,
  tabela1.orgao_lotacao,
  tabela1.carga_horaria,
  tabela1.numero_concurso
FROM 
  dbo.vw_portal_rhf_servidores tabela1
  LEFT JOIN dbo.vw_portal_rhf_servidores tabela2
  ON tabela1.matricula = tabela2.matricula
  AND CONCAT(tabela1.ano, tabela1.mes) < CONCAT(tabela2.ano, tabela2.mes)
  WHERE tabela2.matricula is NULL

I hope it can help anyone facing the same problem, or some similar problem. Thanks to all who were willing to help: D

    
asked by anonymous 21.12.2018 / 18:18

2 answers

0

Here's a solution: 

-- código #1
with servidores_seq as (
SELECT *, 
       seq= row_number() over (partition by matricula order by ano desc, mes desc)
  from dbo.vw_portal_rhf_servidores
  where ...
)
SELECT ano, mes, matricula, cpf, nome_servidor, cargo_ocupado, funcao,
       tipo_vinculo, data_exercicio, data_demissao, situacao_funcional,
       enquadramento_salarial, orgao_lotacao, carga_horaria,
       numero_concurso
  from servidores_seq
  where seq = 1;

In the first WHERE clause you add the selection filters, if needed.

    
26.12.2018 / 18:03
0

It could be something like this: 

SELECT TOP 200
  tabela1.ano,
  tabela1.mes,
  tabela1.matricula,
  tabela1.cpf,
  tabela1.nome_servidor,
  tabela1.cargo_ocupado,
  tabela1.funcao,
  tabela1.tipo_vinculo,
  tabela1.data_exercicio,
  tabela1.data_demissao,
  tabela1.situacao_funcional,
  tabela1.enquadramento_salarial,
  tabela1.orgao_lotacao,
  tabela1.carga_horaria,
  tabela1.numero_concurso
FROM 
  dbo.vw_portal_rhf_servidores tabela1
  ON tabela1.matricula
  AND cast(tabela1.ano as varchar(4)) + "-" + cast(tabela1.mes as varchar(2)) 
  in (select max(cast(tabela2.ano as varchar(4)) + "-" + cast(tabela2.mes as varchar(2)))
  from dbo.vw_portal_rhf_servidores tabela2 where tabela2.matricula = tabela1.matricula)
    
21.12.2018 / 19:31
Problem with loop (v-for) Creation of flask-sqlalchemy tables