Return results for all months of the interval

Question

I own the following query:

SELECT 
    CAST(SUM(CASE WHEN s2.name LIKE '%Servidores' THEN (ta2.time_unit * 1.5)/60 ELSE ta2.time_unit/60 END) as DECIMAL(12,2)) AS TEMPO_CORRETO,
    MONTHNAME(ta2.change_time) AS MES,
    MONTH(ta2.change_time) AS NUMEROMES,
    t2.customer_id as EMPRESA
FROM 
    otrs.ticket t2
    LEFT JOIN otrs.time_accounting as ta2 ON t2.id = ta2.ticket_id
    LEFT JOIN otrs.ticket_type as tt2 ON t2.type_id = tt2.id
    LEFT JOIN otrs.service as s2 ON t2.service_id = s2.id   
WHERE
    ta2.change_time between '2018-01-01' AND '2018-04-30' AND
    t2.customer_id = 'ZSCHIMMER SCHWARZ' AND
    tt2.name = 'CONTRATO PCH'
GROUP BY 
    MONTHNAME(ta2.change_time),
    MONTH(ta2.change_time),
    EMPRESA
ORDER BY 
    NUMEROMES

And I’m getting the following result:

But in my query I am selecting the period from January to April, but in January I have no record my need would return the following:

How could I do that?

Answer 1

First you need to generate the sequence of months:

SELECT m.mes, MONTHNAME(STR_TO_DATE(m.mes, '%m')) AS nome_mes
FROM (
SELECT d1+d2*10 AS mes FROM
   (SELECT 0 AS d1 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
     UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 ) t1,
   (SELECT 0 AS d2 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
     UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 ) t2
LIMIT 12 OFFSET 1 ) AS m  

Then you give the select in the months table, with the Outer Join in your data table, it would look something like this:

SELECT 
    CAST(SUM(CASE WHEN s2.name LIKE '%Servidores' THEN (ta2.time_unit * 1.5)/60 ELSE ta2.time_unit/60 END) AS DECIMAL(12,2)) AS TEMPO_CORRETO,
    MONTHNAME(STR_TO_DATE(m.mes, '%m')) AS MES,
    m.mes AS NUMEROMES,
    t2.customer_id AS EMPRESA
FROM 
    SELECT d1+d2*10 AS mes FROM
   (SELECT 0 AS d1 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
     UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 ) t1,
   (SELECT 0 AS d2 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
     UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 ) t2
LIMIT 12 OFFSET 1 ) AS m   
    LEFT JOIN otrs.time_accounting AS ta2 ON MONTH(ta2.change_time) = m.mes
    LEFT JOIN otrs.ticket t2 ON t2.id = ta2.ticket_id
    LEFT JOIN otrs.ticket_type AS tt2 ON t2.type_id = tt2.id
    LEFT JOIN otrs.service AS s2 ON t2.service_id = s2.id   
WHERE
    ta2.change_time BETWEEN '2018-01-01' AND '2018-04-30' AND
    t2.customer_id = 'ZSCHIMMER SCHWARZ' AND
    tt2.name = 'CONTRATO PCH'
GROUP BY 
    MONTHNAME(ta2.change_time),
    MONTH(ta2.change_time),
    EMPRESA
ORDER BY 
    NUMEROMES

ps. I did not take into account the year

Update: https://www.db-fiddle.com/f/2ZF2RRDH8DMb7QyrfBx1Vg/0

After a lot of conversation (see chat) I arrived at a query that I believe is the solution:

select

m.mes,
CAST(SUM(CASE WHEN s.name LIKE '%Servidores' THEN (coalesce(ta.time_unit,0) * 1.5)/ 60 ELSE coalesce(ta.time_unit,0)/60 END) AS DECIMAL(12, 2)) AS TEMPO_CORRETO,
tk2.customer_id

From

(Select 1 as mes union Select 2 union Select 3 union Select 4 union Select 5 union Select 6 union Select 7 union Select 8 union Select 9 union Select 10 union Select 11 union Select 12) as m

left outer join time_accounting ta on MONTH(ta.change_time) = m.mes 
left outer join ticket tk2 on ta.ticket_id = tk2.id 
                           and tk2.customer_id = 'ZSCHIMMER SCHWARZ' 
                           and tk2.type_id = 11
left outer join service AS s ON tk2.service_id = s.id

WHERE m.mes BETWEEN MONTH('2018-01-01') AND MONTH('2018-07-30')


GROUP BY m.mes,tk2.customer_id

order by m.mes

Available on Dbfiddle

EDIT: 28/05/2018

I redid the query, this time showing the client name on all lines, even if there are no records in the month:

SELECT
MONTHNAME(STR_TO_DATE(meses.mes, '%m')) AS MES,
meses.mes as mes_numero,
CAST(SUM(CASE WHEN s2.name LIKE '%Servidores' THEN ( coalesce(ta.time_unit,0) * 1.5)/ 60 ELSE coalesce(ta.time_unit,0)/60 END) AS DECIMAL(12, 2)) AS TEMPO_CORRETO,
meses.customer_id as empresa
FROM
(Select distinct
  m.mes,
  t2.customer_id
From (SELECT d1+d2*10 AS mes FROM 
(SELECT 0 AS d1 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 ) t1, 
(SELECT 0 AS d2 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 ) t2 
LIMIT 12 OFFSET 1 ) AS m
Cross Join ticket t2
WHERE t2.customer_id = 'ZSCHIMMER SCHWARZ') as meses
LEFT JOIN ticket t on t.customer_id = meses.customer_id
                   and t.type_id = 11
LEFT JOIN time_accounting ta on ta.ticket_id = t.id 
                             and MONTH(ta.change_time) = meses.mes
                             and ta.change_time BETWEEN '2018-01-01' AND '2018-07-30'

LEFT JOIN service AS s2 ON t.service_id = s2.id
WHERE meses.mes BETWEEN MONTH('2018-01-01') AND MONTH('2018-07-30')
GROUP BY   meses.mes, meses.customer_id    
ORDER BY meses.mes

Upshot:

mes_numero  TEMPO_CORRETO   empresa
1           0               ZSCHIMMER SCHWARZ
2           7.39            ZSCHIMMER SCHWARZ
3           23.29           ZSCHIMMER SCHWARZ
4           3.18            ZSCHIMMER SCHWARZ
5           0               ZSCHIMMER SCHWARZ
6           0               ZSCHIMMER SCHWARZ
7           0               ZSCHIMMER SCHWARZ

I put in the Dbfiddle

Answer 2

You can create a table of months, since it would make your life easier:

CREATE TABLE mes (
  id INT,
  mes VARCHAR(10)
);

INSERT INTO mes VALUES(1, 'JANEIRO'),
                      (2, 'FEVEREIRO'),
                      (3, 'MARÇO'),
                      (4, 'ABRIL'),
                      (5, 'MAIO'),
                      (6, 'JUNHO'),
                      (7, 'JULHO'),
                      (8, 'AGOSTO'),
                      (9, 'SETEMBRO'),
                      (10, 'OUTUBRO'),
                      (11, 'NOVEMBRO'),
                      (12, 'DEZEMBRO');

After that use this table created directly on FROM, so you can show the values independent of the links with the other tables:

...
FROM mes m
...

In the next step link the other tables with LEFT JOIN, which will limit the obligation:

...
LEFT JOIN time_accounting AS ta2 ON MONTH(ta2.change_time) = m.id
LEFT JOIN ticket t2 ON t2.id = ta2.ticket_id
LEFT JOIN ticket_type AS tt2 ON t2.type_id = tt2.id
LEFT JOIN service AS s2 ON t2.service_id = s2.id
...

In the WHERE you should consider the tables used in LEFT may have OR NOT records, therefore one should check whether it is NULL:

...
WHERE (t2.id IS NULL OR t2.customer_id = 'ZSCHIMMER SCHWARZ') 
  AND (tt2.id IS NULL OR tt2.name = 'CONTRATO PCH') 
...

I advise that the date filter is ugly per month, so that what is not inside the filter will not be shown:

...
AND m.id BETWEEN MONTH('2018-01-01') AND MONTH('2018-07-30')
...

To query resulting:

SELECT m.id,
       m.mes,
       CAST(SUM(CASE
                  WHEN s2.name LIKE '%Servidores' THEN (ta2.time_unit * 1.5) / 60
                  ELSE ta2.time_unit/60
                END) AS DECIMAL(12, 2)) AS TEMPO_CORRETO,
       t2.customer_id AS EMPRESA
  FROM mes m
  LEFT JOIN time_accounting AS ta2 ON MONTH(ta2.change_time) = m.id
  LEFT JOIN ticket t2 ON t2.id = ta2.ticket_id
  LEFT JOIN ticket_type AS tt2 ON t2.type_id = tt2.id
  LEFT JOIN service AS s2 ON t2.service_id = s2.id
 WHERE (t2.id IS NULL OR t2.customer_id = 'ZSCHIMMER SCHWARZ')
   AND (tt2.id IS NULL OR tt2.name = 'CONTRATO PCH')
   AND m.id BETWEEN MONTH('2018-01-01') AND MONTH('2018-07-30')
 GROUP BY m.mes, m.id, t2.customer_id
 ORDER BY m.id

Answer 3

If you reverse the table otrs.ticket t2 with the otrs.time_accounting ta2 wouldn’t work?

Edited

SELECT 
    CAST(SUM(CASE WHEN s2.name LIKE '%Servidores' THEN (ta2.time_unit * 1.5)/60 ELSE ta2.time_unit/60 END) as DECIMAL(12,2)) AS TEMPO_CORRETO,
    MONTHNAME(ta2.change_time) AS MES,
    MONTH(ta2.change_time) AS NUMEROMES,
    t2.customer_id as EMPRESA
FROM 
    otrs.time_accounting ta2
    LEFT JOIN otrs.ticket t2 ON t2.id = ta2.ticket_id
    LEFT JOIN otrs.ticket_type as tt2 ON t2.type_id = tt2.id
    LEFT JOIN otrs.service as s2 ON t2.service_id = s2.id   
WHERE
    ta2.change_time between '2018-01-01' AND '2018-04-30' AND
    coalesce(t2.customer_id, 'ZSCHIMMER SCHWARZ') = 'ZSCHIMMER SCHWARZ' AND
    coalesce(tt2.name, 'CONTRATO PCH')            = 'CONTRATO PCH'
GROUP BY 
    MONTHNAME(ta2.change_time),
    MONTH(ta2.change_time),
    EMPRESA
ORDER BY 
    NUMEROMES

Try to use the coalesce also, try to give a suitable for your case to see if it works.

Answer 4

Correction: Add another condition AND CAST(SUM(CASE WHEN s2.name LIKE '%Servidores' THEN (ta2.time_unit * 1.5)/60 ELSE ta2.time_unit/60 END) as DECIMAL(12,2)) > 0

SELECT 
    CAST(SUM(CASE WHEN s2.name LIKE '%Servidores' THEN (ta2.time_unit * 1.5)/60 ELSE ta2.time_unit/60 END) as DECIMAL(12,2)) AS TEMPO_CORRETO,
    MONTHNAME(ta2.change_time) AS MES,
    MONTH(ta2.change_time) AS NUMEROMES,
    t2.customer_id as EMPRESA
FROM 
    otrs.ticket t2
    LEFT JOIN otrs.time_accounting as ta2 ON t2.id = ta2.ticket_id
    LEFT JOIN otrs.ticket_type as tt2 ON t2.type_id = tt2.id
    LEFT JOIN otrs.service as s2 ON t2.service_id = s2.id   
WHERE
    ta2.change_time between '2018-01-01' AND '2018-04-30' AND
    t2.customer_id = 'ZSCHIMMER SCHWARZ' AND
    tt2.name = 'CONTRATO PCH' AND
    CAST(SUM(CASE WHEN s2.name LIKE '%Servidores' THEN (ta2.time_unit * 1.5)/60 ELSE ta2.time_unit/60 END) as DECIMAL(12,2)) > 0
GROUP BY 
    MONTHNAME(ta2.change_time),
    MONTH(ta2.change_time),
    EMPRESA
ORDER BY 
    NUMEROMES