How to make a select taking into account if the record exists, if it does not exist return null or empty

Asked

Viewed 72 times

2

I have a code that returns the activities that have been completed within a 12 month period.

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

Upshot

inserir a descrição da imagem aqui

However there is a flaw in this code, the case is if I registered a company and reported that the activities will start from July for example, from January to June should not appear as pending, should appear as Null or Empty.

My question is as follows as I can inform in the query to check if there is any activity on that date and if it has not returned null or empty.

mysql sql mysqli
  • 1

    You could not exchange the "PENDING" then for "NULL", in this case?

  • TRY CASE WHEN (SUM(CASE WHEN dt_maturity IS NULL THEN ELSE (CASE WHEN MONTH(dt_maturity) = 1 THEN 1 ELSE 0 END))) > 0 THEN 'OK' ELSE 'PENDING' END AS MES_1,

  • @Motta - Apparently there is some error in this query did not run.

  • CASE WHEN (SUM(CASE WHEN dt_maturity IS NULL THEN NULL ELSE (CASE WHEN MONTH(dt_maturity) = 1 THEN 1 ELSE 0 END) END)) > 0 THEN 'OK' ELSE 'PENDING' END AS MES_1,

  • the basic idea is if it is null to generate a null because null does not sum

  • The idea is good, but it didn’t work, the closest thing was CASE WHEN (SUM(CASE WHEN MONTH(dt_maturity) = 1 THEN 1 ELSE NULL END)) > 0 THEN STATUS ELSE NULL END AS MES_1

  • What went wrong ?

Show 2 more comments

1 answer

1

To be null you do not return the else or return null.

Similarly, in the sum, you do not need to return 0.

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

Browser other questions tagged mysql sql mysqli

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