Fill in the name even if the name does not exist in the table

Asked

Viewed 51 times

1

How can I replace the section below, so that the name of the activities of tbl_atividades_por_dep, even if the record does not exist in the tbl_atividades, the first select with distinct is what returns the name of the activities.

In the second select if the tipo_atividade does not exist it does not return the activity name as in the first query.

AND tipo_activity IN ('RECALCULOS TRIBUTES', 'REPRISALS', 'INSTALLMENTS', 'RECTIFICATIONS SPED', 'INFORMES MANAGERIAL','SERVICE INSPECTION')

SELECT DISTINCT atividade 
FROM tbl_atividades_por_dep AS a
WHERE atividade IN ('RECALCULOS TRIBUTOS','REAPURAÇÕES','PARCELAMENTOS','RETIFICAÇÕES SPED','INFORMES GERENCIAIS','ATENDIMENTO FISCALIZAÇÃO')

SELECT tipo_atividade,
SUM(CASE WHEN MONTH(DT_VENCIMENTO) = MONTH(CURDATE()) AND MONTH(DT_FIM) = MONTH(CURDATE()) THEN 1 ELSE 0 END) AS este_mes,
SUM(CASE WHEN MONTH(DT_VENCIMENTO) = MONTH(CURDATE())-1 AND MONTH(DT_FIM) = MONTH(CURDATE())-1 THEN 1 ELSE 0 END) AS mes_passado
FROM tbl_atividades
WHERE departamento = 'FISCAL' AND empresa_origem = 'MATRIZ'
AND tipo_atividade IN ('RECALCULOS TRIBUTOS','REAPURAÇÕES','PARCELAMENTOS','RETIFICAÇÕES SPED','INFORMES GERENCIAIS','ATENDIMENTO FISCALIZAÇÃO')
AND (STATUS = 'CONCLUIDO' OR STATUS = 'CONCLUIDO_VENCIDO')
AND YEAR(DT_VENCIMENTO) = YEAR(CURDATE())
AND YEAR(DT_FIM) = YEAR(CURDATE())
GROUP BY tipo_atividade
mysql sql

  • I didn’t quite understand your question, however, I think your problem is in the condition tipo_activity IN... one of the solutions might be to apply a left Join

1 answer

0

Try using the command CASE WHEN at your request.

The same will basically fill in with the value you want, if your column is with a certain value.

SELECT 
      (CASE WHEN COLUNA IS NULL 
            THEN 'ATIVIDADE' 
            ELSE COLUNA END)  AS ATIVIDADE 
  FROM tbl_atividades

Browser other questions tagged mysql sql

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