Incorrect grouping of lines

Question

Hello someone can help me, I’m trying to bring a company report and activity made in 12 months, a company and an activity was made during 12 months and I want to know each activity if it was made in January, February, March etc, the problem is that this not grouping by company correctly the month to month.

My code.

SELECT   
empresas,
tipo_atividade,
CASE WHEN MONTH(dt_fim) = '1'   THEN IFNULL('OK','PENDENTE')  ELSE  'PENDENTE' END AS MES_1,
CASE WHEN MONTH(dt_fim) = '2'   THEN IFNULL('OK','PENDENTE')  ELSE  'PENDENTE' END AS MES_2,
CASE WHEN MONTH(dt_fim) = '3'   THEN IFNULL('OK','PENDENTE')  ELSE  'PENDENTE' END AS MES_3,
CASE WHEN MONTH(dt_fim) = '4'   THEN IFNULL('OK','PENDENTE')  ELSE  'PENDENTE' END AS MES_4,
CASE WHEN MONTH(dt_fim) = '5'   THEN IFNULL('OK','PENDENTE')  ELSE  'PENDENTE' END AS MES_5,
CASE WHEN MONTH(dt_fim) = '6'   THEN IFNULL('OK','PENDENTE')  ELSE  'PENDENTE' END AS MES_6,
CASE WHEN MONTH(dt_fim) = '7'   THEN IFNULL('OK','PENDENTE')  ELSE  'PENDENTE' END AS MES_7,
CASE WHEN MONTH(dt_fim) = '8'   THEN IFNULL('OK','PENDENTE')  ELSE  'PENDENTE' END AS MES_8,
CASE WHEN MONTH(dt_fim) = '9'   THEN IFNULL('OK','PENDENTE')  ELSE  'PENDENTE' END AS MES_9,
CASE WHEN MONTH(dt_fim) = '10'   THEN IFNULL('OK','PENDENTE')  ELSE  'PENDENTE' END AS MES_10,
CASE WHEN MONTH(dt_fim) = '11'   THEN IFNULL('OK','PENDENTE')  ELSE  'PENDENTE' END AS MES_11,
CASE WHEN MONTH(dt_fim) = '12'   THEN IFNULL('OK','PENDENTE')  ELSE  'PENDENTE' END AS MES_12

FROM tbl_atividades
WHERE 
YEAR(dt_fim) = '2019' AND 
(STATUS = 'CONCLUIDO' OR STATUS = 'CONCLUIDO_VENCIDO')
ORDER BY empresas, tipo_atividade;

The result is as follows

When I use the GROUP BY COMPANIES clause the result is wrong.

I also tried to

GROUP BY EMPRESAS
GROUP BY EMPRESAS, TIPO_ATIVIDADE
GROUP BY TIPO_ATIVIDADE

All went wrong , what I need to do to properly display by company and activity in the month to month in a single line.

Answer 1

One of the ways to solve this would be to count how many records you have dt_fim in the corresponding month and meeting the other criteria of WHERE, after arriving at this value, you check if there is any record in that month, if it exists, fill in with OK, otherwise PENDENTE, something like:

SELECT   
  empresas,
  tipo_atividade,
  CASE WHEN (SUM(CASE WHEN MONTH(dt_fim) = 1 THEN 1 ELSE 0 END)) > 0 THEN 'OK' ELSE 'PENDENTE' END AS MES_1,
  CASE WHEN (SUM(CASE WHEN MONTH(dt_fim) = 2 THEN 1 ELSE 0 END)) > 0 THEN 'OK' ELSE 'PENDENTE' END AS MES_2,
  CASE WHEN (SUM(CASE WHEN MONTH(dt_fim) = 3 THEN 1 ELSE 0 END)) > 0 THEN 'OK' ELSE 'PENDENTE' END AS MES_3,
  CASE WHEN (SUM(CASE WHEN MONTH(dt_fim) = 4 THEN 1 ELSE 0 END)) > 0 THEN 'OK' ELSE 'PENDENTE' END AS MES_4,
  CASE WHEN (SUM(CASE WHEN MONTH(dt_fim) = 5 THEN 1 ELSE 0 END)) > 0 THEN 'OK' ELSE 'PENDENTE' END AS MES_5,
  CASE WHEN (SUM(CASE WHEN MONTH(dt_fim) = 6 THEN 1 ELSE 0 END)) > 0 THEN 'OK' ELSE 'PENDENTE' END AS MES_6,
  CASE WHEN (SUM(CASE WHEN MONTH(dt_fim) = 7 THEN 1 ELSE 0 END)) > 0 THEN 'OK' ELSE 'PENDENTE' END AS MES_7,
  CASE WHEN (SUM(CASE WHEN MONTH(dt_fim) = 8 THEN 1 ELSE 0 END)) > 0 THEN 'OK' ELSE 'PENDENTE' END AS MES_8,
  CASE WHEN (SUM(CASE WHEN MONTH(dt_fim) = 9 THEN 1 ELSE 0 END)) > 0 THEN 'OK' ELSE 'PENDENTE' END AS MES_9,
  CASE WHEN (SUM(CASE WHEN MONTH(dt_fim) = 10 THEN 1 ELSE 0 END)) > 0 THEN 'OK' ELSE 'PENDENTE' END AS MES_10,
  CASE WHEN (SUM(CASE WHEN MONTH(dt_fim) = 11 THEN 1 ELSE 0 END)) > 0 THEN 'OK' ELSE 'PENDENTE' END AS MES_11,
  CASE WHEN (SUM(CASE WHEN MONTH(dt_fim) = 12 THEN 1 ELSE 0 END)) > 0 THEN 'OK' ELSE 'PENDENTE' END AS MES_12
FROM 
  tbl_atividades
WHERE 
  YEAR(dt_fim) = '2019' AND (STATUS = 'CONCLUIDO' OR STATUS = 'CONCLUIDO_VENCIDO')
GROUP BY 
  empresas, tipo_atividade;

The SUM(CASE WHEN MONTH(dt_fim) = 1 THEN 1 ELSE 0 END) counts as I reported.