Displaying quarterly data until the current date POSTGRESQL

Question

Displaying quarterly data until the current date POSTGRESQL

Navigation

#1 by (4 votes)

0

As the SQL Fiddle , I'm displaying the quarterly data of a table, but that query is showing only when it has value in the quarter (3, 6, 9 or 12), when it has value for example in the current month (5), nothing is displayed, then ...

How can I adjust my code SQL so that quarterly values are displayed until the current date?

NOTE: Only the last month that would be out of the "default" quarter in the case, to suit until the last record.

postgresql

asked by anonymous 12.05.2017 / 22:26

1 answer

I have one form inside the other and I can not get the most internal form Does the tabindex attribute work in the opera browser?

score 4 · Accepted Answer

This happens because in where you were filtering exactly this situation: only "divisible" months by 3.

AND cast(to_char("HistoricoIndicador"."dt_criado",'mm') as int) % 3 = 0

If you understand correctly, you need to list the values from months to months, in which case you can use the extract function by retrieving 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 from select and group by .