Group a date field in months

Asked

Viewed 37 times

1

Good afternoon, could someone help me?

How can I put this query to display from January to December correctly?

SELECT   
EMPRESA,ATIVIDADE,DATA,STATUS
FROM tbl_atividades
WHERE YEAR(dt_vencimento) = '2019'
GROUP BY empresas, tipo_atividade

Expected result.

inserir a descrição da imagem aqui

I tried also as follows however the result was that it does not return the months in the month to month, it takes the last record that it finds, and if I play the months in group by , it creates several more activities.

SELECT   
empresas,
tipo_atividade,
(SELECT STATUS WHERE MONTH(dt_vencimento)=1) mes1,
(SELECT STATUS WHERE MONTH(dt_vencimento)=2) mes2,
(SELECT STATUS WHERE MONTH(dt_vencimento)=3) mes3,
(SELECT STATUS WHERE MONTH(dt_vencimento)=4) mes4,
(SELECT STATUS WHERE MONTH(dt_vencimento)=5) mes5,
(SELECT STATUS WHERE MONTH(dt_vencimento)=6) mes6,
(SELECT STATUS WHERE MONTH(dt_vencimento)=7) mes7,
(SELECT STATUS WHERE MONTH(dt_vencimento)=8) mes8,
(SELECT STATUS WHERE MONTH(dt_vencimento)=9) mes9,
(SELECT STATUS WHERE MONTH(dt_vencimento)=10) mes10,
(SELECT STATUS WHERE MONTH(dt_vencimento)=11) mes11,
(SELECT STATUS WHERE MONTH(dt_vencimento)=12) mes12
FROM 
tbl_atividades
WHERE 
YEAR(dt_vencimento) = '2019'
GROUP BY 
empresas,tipo_atividade

inserir a descrição da imagem aqui

  • Are you sure you have the status of 12 months for all companies? Because your query is correct, or depending on how you use these records, GROUP BY is causing the error. Because the strange is to come NULL. If something were wrong, it would be date/sort/limit. If you can, put an example in sqlfiddle or db-fiddle.

  • Put your full code here: http://sqlfiddle.com/

  • https://www.hardware.com.br/comunidade/vendas-mes/1332229/ pivot com case

No answers

Browser other questions tagged

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