Doubt with a query (SQL Server)

Asked

Viewed 69 times

0

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.

inserir a descrição da imagem aqui

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):

inserir a descrição da imagem aqui

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

  • Matheus is all good? Post your table template, your current query and leave a return example you’d like to get so we can help you. Here on the site give preference for codes than images, otherwise you run the risk of your question being closed, okay? Hug :)

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

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)
  • Didn’t work buddy. Gives the following message: "Incorrect syntax near the keyword 'ON' "

  • ON Tabela1.matricula = table2.matricula

Browser other questions tagged

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