redmine_worked_time.sql

SELECT
  CONCAT(u.firstname, ' ', u.lastname) AS nome,
  CONCAT((CASE t.tmonth
  WHEN 1 THEN 'Janeiro'
  WHEN 2 THEN 'Fevereiro'
  WHEN 3 THEN 'Março'
  WHEN 4 THEN 'Abril'
  WHEN 5 THEN 'Maio'
  WHEN 6 THEN 'Junho'
  WHEN 7 THEN 'Julho'
  WHEN 8 THEN 'Agosto'
  WHEN 9 THEN 'Setembro'
  WHEN 10 THEN 'Outubro'
  WHEN 11 THEN 'Novembro'
  WHEN 12 THEN 'Dezembro'
  ELSE NULL
  END) , '/', t.tyear) AS periodo,
  (CASE t.tmonth
  WHEN 1 THEN (20*8)
  WHEN 2 THEN (20*8)
  WHEN 3 THEN (20*8)
  WHEN 4 THEN (20*8)
  WHEN 5 THEN (20*8)
  WHEN 6 THEN (20*8)
  WHEN 7 THEN (20*8)
  WHEN 8 THEN (20*8)
  WHEN 9 THEN (20*8)
  WHEN 10 THEN (20*8)
  WHEN 11 THEN (20*8)
  WHEN 12 THEN (17*8)
  ELSE NULL
  END) as meta_mes,
  ROUND(SUM(t.hours),2) as horas_trabalhadas,
  ROUND((SUM(t.hours)/(SELECT meta_mes))*100,2) as progresso_mes,
  ROUND(GREATEST((SELECT meta_mes)-(ROUND(SUM(t.hours),2)),0),2) as horas_faltantes
FROM
users u
INNER JOIN time_entries t ON u.id = t.user_id

WHERE
  t.tyear = 2017 AND
  t.tmonth BETWEEN 1 AND 12
GROUP BY
u.id,
  u.firstname,
  u.lastname,
  periodo
ORDER BY
u.firstname,
  u.lastname,
  t.tyear,
  t.tmonth