This is because in where
you were filtering exactly this situation: only "divisible" months for 3.
AND cast(to_char("HistoricoIndicador"."dt_criado",'mm') as int) % 3 = 0
If I understand correctly, you need to list the values of months per quarter, in which case you can use the function extract
recovering the quarter number using the interval quarter
.
SELECT
"IndicadorPeriodo"."id" AS "IndicadorPeriodo__id",
"Meta"."meta" AS "Meta__meta",
(SUM("problemas") / SUM("total")) AS problemas,
extract(quarter from "HistoricoIndicador"."dt_criado") as periodo,
to_char("HistoricoIndicador"."dt_criado", 'yyyy/mm') as mes_ano,
case
when extract(month from "HistoricoIndicador"."dt_criado") <= 6 then '1º Sem.'
else '2º Sem.'
end as semestre
FROM "indicador_funcionarios" AS "IndicadorFuncionario"
LEFT JOIN "indicadors" AS "Indicador"
ON ("IndicadorFuncionario"."indicador_id" = "Indicador"."id")
LEFT JOIN "historico_indicadors" AS "HistoricoIndicador"
ON ("HistoricoIndicador"."indicador_id" = "Indicador"."id")
LEFT JOIN "indicador_periodos" AS "IndicadorPeriodo"
ON ("Indicador"."indicador_periodo_id" = "IndicadorPeriodo"."id")
LEFT JOIN "indicador_metas" AS "IndicadorMeta"
ON ("IndicadorMeta"."indicador_id" = "Indicador"."id")
LEFT JOIN "metas" AS "Meta"
ON ("IndicadorMeta"."meta_id" = "Meta"."id")
WHERE
"IndicadorFuncionario"."funcionario_id" = 10131
AND "IndicadorPeriodo"."id" = 'T'
AND "Indicador"."id" = 1
GROUP BY
"IndicadorPeriodo"."id",
"Meta"."meta",
"periodo",
"mes_ano",
"semestre"
ORDER BY "periodo";
If you need to display a row with the totals for each quarter, simply remove the column mes_ano
of select
and of group by
.
Trying to understand, you have a table of
indicadors
which has periodically registered instances in thehistorico_indicadors
; and now wants to display all the rows in this table that have theirdt_criado
within the current quarter, which means months 1, 2 and 3 for the first quarter, 4, 5 and 6 for the second, 7, 8 and 9 for the third, and 10, 11 and 12 for the fourth, correct?– Wtrmute
Yes. (I hadn’t seen your comment before, Sorry!)
– Marcos Henzel