Doubt with a query (SQL Server)

Question

Good afternoon!

I have a set of information in an SQL Server database (I’m still learning SQL Server), and need to filter the information that is returned to me. They are information of employees of a company (registration, Cpf, name, position, situation, etc). Only that I also own 2 fields called year and month, referring to the year and month that employee worked. I would like to receive only the last month of the last year that employee worked, and not every month. For example, there is one employee who worked until the 6th of 2018 until the 10th of 2018, and another who worked from the 2nd of 2018 until the 12th of 2018. Being that for each month work his situation may be different (in one month he may be Active, in another Vacation month, in another Fired, etc)

I’ve tried using GROUP BY and DISTINCT, but the way I used it didn’t work. The image below illustrates a little of my doubt.

From now on I thank you all.

Edit: The query I’m currently using is this (I adapted it from an example I found on a site, but it didn’t help me solve the problem):

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
  LEFT JOIN dbo.vw_portal_rhf_servidores tabela2
  ON tabela1.matricula = tabela2.matricula
  AND tabela1.ano < tabela2.ano
  AND tabela1.mes < tabela2.mes

I don’t own the table template because I only get a view from another place.

The result that I would like to receive would be the image below, circled in yellow (I know it got weird, but I can’t exemplify it in a better way):

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 who is facing the same problem, or a similar problem. Thank you to all who are willing to help :D

Answer 1

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 necessary.

Answer 2

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)