Select months even without records

6

I need to select the data stored in the database and separate them by month, but when the month does not have data yet, the month is not returned.

I need the month to be returned with the value zero but to bring the month anyway.

My Query looks like this:

SELECT 
  (year(article.content_path)) as anoAtual,
  MONTHNAME(STR_TO_DATE(month(article.content_path), '%m')) AS MES,
  TIME_FORMAT(SEC_TO_TIME(Sum(time_accounting.time_unit)*60),"%H:%i") as tempo_total,
  concat(users.first_name, ' ',customer_user.last_name) as usuario,
  customer_user.email
FROM 
  article,
  time_accounting,
  users,
  customer_user
where 
  article.id = time_accounting.article_id and
  article.create_by = users.id and
  EXTRACT(YEAR FROM article.content_path) IN (2018) and 
  users.first_name = customer_user.first_name and 
  users.last_name like concat(customer_user.last_name,'%') and
  customer_user.customer_id = empresa and
  users.valid_id = 1 and
  customer_user.email in (emails)
group by 
  article.create_by,
  year(article.content_path),
  MES
order by 
  article.create_by,
  year(article.content_path),
  month(article.content_path)

I get the following return:

InthiscaseIneedthemonthofDecemberstilltocome,eventhoughIdonothaveanyrecordsyetthismonth.I'vetriedsomethingsimilarwithwhatIneedwithasimplerQuery:

SELECTmonth(article.change_time)asmes,SEC_TO_TIME(SUM(IFNULL(TIME_TO_SEC(STR_TO_DATE(TIMEDIFF((SELECTvalue_dateFROMotrs.dynamic_field_valuewhereobject_idin(t2.object_id)andfield_id=79),(SELECTvalue_dateFROMotrs.dynamic_field_valuewhereobject_idin(t2.object_id)andfield_id=78)),'%H:%i:%s')),0)))astempoDeslocamentoFROMotrs.dynamic_field_valuet2,otrs.articlewhereotrs.article.id=t2.object_idandyear(article.change_time)=2018andarticle.create_by=9andfield_idin(78)groupbymonth(article.change_time);

Butevenso,itonlyreturnstherecordsinthemonthsthathaverecords.

9 and month 12 , but IFNULL should not handle this?

    
asked by anonymous 22.11.2018 / 18:07

3 answers

5

Try it this way:

SELECT      YEAR(IFNULL(A.content_path, NOW()))                                                     AS anoAtual
        ,   MONTHNAME(M.Mes)                                                        AS Mes
        ,   TIME_FORMAT(SEC_TO_TIME(SUM(IFNULL(TA.time_unit, 0)) * 60), "%H:%i")    AS tempo_total
        ,   CONCAT(U.first_name, ' ', CU.last_name)                                 AS usuario
        ,   CU.email
FROM        (
                SELECT          1   AS Mes
                UNION SELECT    2   AS Mes
                UNION SELECT    3   AS Mes
                UNION SELECT    4   AS Mes
                UNION SELECT    5   AS Mes
                UNION SELECT    6   AS Mes
                UNION SELECT    7   AS Mes
                UNION SELECT    8   AS Mes
                UNION SELECT    9   AS Mes
                UNION SELECT    10  AS Mes
                UNION SELECT    11  AS Mes
                UNION SELECT    12  AS Mes
            ) AS M
LEFT JOIN   article         A   ON  MONTH(A.content_path) = M.Mes
LEFT JOIN   time_accounting TA  ON  TA.article_id               = A.id
LEFT JOIN   users           U   ON  U.id                        = A.create_by
LEFT JOIN   customer_user   CU  ON  CU.first_name               = U.first_name
                                AND CU.customer_id              = empresa
WHERE       EXTRACT(YEAR FROM A.content_path)   IN (2018)
        and U.last_name                         LIKE CONCAT(CU.last_name, '%')
        and U.valid_id                          = 1 
        and CU.email                            IN (emails)
GROUP BY    A.create_by
        ,   YEAR(A.content_path)
        ,   M.Mes
ORDER BY    A.create_by
        ,   YEAR(A.content_path)
        ,   M.Mes

The idea is to make a JOIN with a table already with every month and then present the information of each one.

The IFNULL has been set if one of the months has no information to display the value "0" in the tempo_total column.

    
22.11.2018 / 19:06
2

You can use all your query in LEFT JOIN :

SELECT MONTHNAME(m.id) AS mes, x.*
  FROM (
    SELECT 1 AS id
    UNION SELECT 2 AS id
    UNION SELECT 3 AS id
    UNION SELECT 4 AS id
    UNION SELECT 5 AS id
    UNION SELECT 6 AS id
    UNION SELECT 7 AS id
    UNION SELECT 8 AS id
    UNION SELECT 9 AS id
    UNION SELECT 10 AS id
    UNION SELECT 11 AS id
    UNION SELECT 12 AS id
  ) AS m
      LEFT JOIN (
        SELECT (YEAR(a.content_path)) as anoAtual,
               MONTHNAME(STR_TO_DATE(MONTH(article.content_path), '%m')) AS MES,
               TIME_FORMAT(SEC_TO_TIME(SUM(ta.time_unit) * 60), "%H:%i") as tempo_total,
               CONCAT(u.first_name, ' ', cu.last_name) as usuario,
               cu.email
          FROM article a
               INNER JOIN time_accounting ta ON a.id = ta.article_id
               INNER JOIN users u ON a.create_by = u.id
               INNER JOIN customer_user cu ON u.first_name = cu.first_name
         WHERE EXTRACT(YEAR FROM a.content_path) IN (2018)
           AND u.last_name LIKE concat(cu.last_name,'%')
           AND cu.customer_id = empresa
           AND u.valid_id = 1
           AND cu.email IN (emails)
         GROUP BY a.create_by,
                  YEAR(a.content_path),
                  MES
         ORDER BY a.create_by,
                  YEAR(a.content_path),
                  MONTH(a.content_path)
      ) x ON MONTHNAME(m.id) = x.mes
    
28.11.2018 / 12:13
0

Since values will always be returned for the 12 months of the year, the most practical way I would look at would be to create a temporary table with values already queried by adding a line with time value 0 for each month zeroed . Hence it would only be to consult the sum on this table; which has value, would add two rows ( zero plus the total value), which would not return only the 0 (to facilitate, would add to the current one numeric month column ):

--variável para utilização do ano em mais de um ponto
set @anoConsulta = 2018;

--criação da tabela com os dados que você já tem {mesmo select adicionando a coluna criador que é usada na ordenação, e MES_NUMERICO para facilitar o filtro}

CREATE TEMPORARY TABLE IF NOT EXISTS tmpTab AS (
    SELECT 
      (year(article.content_path)) as anoAtual,
      MONTHNAME(STR_TO_DATE(month(article.content_path), '%m')) AS MES,
      month(article.content_path) AS MES_NUMERICO,
      TIME_FORMAT(SEC_TO_TIME(Sum(time_accounting.time_unit)*60),"%H:%i") as tempo_total,
      concat(users.first_name, ' ',customer_user.last_name) as usuario,
      customer_user.email as email,
      article.create_by as criador
    FROM 
      article,
      time_accounting,
      users,
      customer_user
    where 
      article.id = time_accounting.article_id and
      article.create_by = users.id and
      EXTRACT(YEAR FROM article.content_path) IN (@anoConsulta) and 
      users.first_name = customer_user.first_name and 
      users.last_name like concat(customer_user.last_name,'%') and
      customer_user.customer_id = empresa and
      users.valid_id = 1 and
      customer_user.email in (emails)
    group by 
      article.create_by,
      year(article.content_path),
      MES);

-- adicionar à tabela os meses zerados
DECLARE contador INT DEFAULT 1;

WHILE (contador <= 12) DO
  IF NOT EXISTS (SELECT 1 FROM tmpTab WHERE MES_NUMERICO = contador) THEN
    INSERT INTO tmpTab (anoAtual, MES, MES_NUMERICO, tempo_total, usuario, email, criador)
    VALUES (@anoConsulta, contador, MONTHNAME(concat(@anoConsulta','-',contador,'-01'), 0, 'nome qualquer', '[email protected]');
  END IF;

  SET contador = contador + 1;
END WHILE;

--agora todos os meses estão na tabela temporária, basta retorná-los
SELECT anoAtual, MES, tempo_total, usuario, email
FROM tmpTab
ORDER BY criador, anoAtual, MES;
    
23.11.2018 / 15:42