Displaying quarterly data up to the current POSTGRESQL date

Asked

Viewed 410 times

0

According to the SQL Fiddle, I am displaying the quarterly data of a table, however, this query is only displaying when it has value in the quarter (3, 6, 9 or 12), when it has value for example in the current month (5), is not displayed anything, then...

How can I adjust my code SQL to display quarterly values up to the current date?

OBS.: Only the last month that would be out of the "standard" quarter in the case, to fit until the last record.

postgresql

  • Trying to understand, you have a table of indicadors which has periodically registered instances in the historico_indicadors; and now wants to display all the rows in this table that have their dt_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?

  • Yes. (I hadn’t seen your comment before, Sorry!)

1 answer

4


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.

  • That’s exactly what I need Camilo, so in period 1, I need to display the sum of the problems in period 1, period 2 and so on... Perfect, thanks !!

  • Dude, one question! If I want to display data Semestrais, how could I do it? I found nothing in the documentation of EXTRACT, if you can supplement the answer, it would be interesting, thanks !

  • 1

    @Marcoshenzel can do with the case, I edited the SQL code with the example.

Browser other questions tagged postgresql

You are not signed in. Login or sign up in order to post.