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.
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
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 :)
– Pedro Paulo