0
Good morning, I need this query to return the unified result, different from what it returns to me, does anyone have any idea how to do this in Postgres?? went until May(May) but continues until the end of the months, my parameter $1 is YEAR .
RETURN QUERY SELECT DC.cod_descricao_status, DC.nome,
(CASE WHEN TEMP.ano IS NULL THEN $1 ELSE TEMP.ano END) AS "ano",
(CASE WHEN TEMP.jan IS NULL THEN 0 ELSE TEMP.jan END) AS "jan",
(CASE WHEN TEMP.fev IS NULL THEN 0 ELSE TEMP.fev END) AS "fev",
(CASE WHEN TEMP.mar IS NULL THEN 0 ELSE TEMP.mar END) AS "mar",
(CASE WHEN TEMP.abr IS NULL THEN 0 ELSE TEMP.abr END) AS "abr",
(CASE WHEN TEMP.mai IS NULL THEN 0 ELSE TEMP.mai END) AS "mai"
FROM public.pla_descricao_status_tarefa AS DC
INNER JOIN
(SELECT S.cod_descricao_status, D.nome, EXTRACT(YEAR FROM S.data_alteracao) AS "ano",
COUNT(CASE WHEN(EXTRACT(MONTH FROM (S.data_alteracao)) = 1) THEN S.data_alteracao END) AS "jan",
COUNT(CASE WHEN(EXTRACT(MONTH FROM (S.data_alteracao)) = 2) THEN S.data_alteracao END) AS "fev",
COUNT(CASE WHEN(EXTRACT(MONTH FROM (S.data_alteracao)) = 3) THEN S.data_alteracao END) AS "mar",
COUNT(CASE WHEN(EXTRACT(MONTH FROM (S.data_alteracao)) = 4) THEN S.data_alteracao END) AS "abr",
COUNT(CASE WHEN(EXTRACT(MONTH FROM (S.data_alteracao)) = 5) THEN S.data_alteracao END) AS "mai",
COUNT(S.data_alteracao) AS TOTAL
FROM (SELECT DISTINCT ON (cod_descricao_status, cod_tarefa)cod_descricao_status, cod_tarefa, data_alteracao FROM public.pla_status_tarefa) AS S
INNER JOIN public.pla_tarefa AS T ON T.cod_tarefa = S.cod_tarefa
INNER JOIN public.pla_descricao_status_tarefa AS D ON D.cod_descricao_status = S.cod_descricao_status
WHERE (S.cod_descricao_status = 1 OR S.cod_descricao_status = 7 OR S.cod_descricao_status = 4) AND T.custo_executante LIKE CONCAT($2,'%') AND T.custo_executante NOT LIKE '101'
AND T.flg_excluido = '0'
AND EXTRACT(YEAR FROM S.data_alteracao) = $1
AND T.cod_tarefa IN (SELECT S1.cod_tarefa FROM public.pla_status_tarefa AS S1 WHERE S1.cod_descricao_status = 1)
GROUP BY S.cod_descricao_status, D.nome, S.data_alteracao)
AS TEMP ON DC.cod_descricao_status = TEMP.cod_descricao_status
WHERE DC.cod_descricao_status = 1 OR DC.cod_descricao_status = 7 OR DC.cod_descricao_status = 4;
END;
he returns to me like this :
(1,"ENV - Enviado",2020,0,0,0,0,0,0,0,0,0,0,1,0)
(1,"ENV - Enviado",2020,0,0,0,0,0,0,0,0,0,0,1,0)
(1,"ENV - Enviado",2020,0,0,0,0,0,0,0,0,0,0,1,0)
(4,"CAN - Cancelado",2020,0,0,0,0,0,0,0,1,0,0,0,0)
(4,"CAN - Cancelado",2020,0,0,0,0,0,0,0,1,0,0,0,0)
(4,"CAN - Cancelado",2020,0,0,0,0,0,0,0,1,0,0,0,0)
(7,"FIN - Finalizado",2020,0,0,0,0,0,0,0,0,1,0,0,0)
(7,"FIN - Finalizado",2020,0,0,0,0,0,0,0,0,1,0,0,0)
(7,"FIN - Finalizado",2020,0,0,0,0,0,0,0,0,1,0,0,0)
(7,"FIN - Finalizado",2020,0,0,0,0,0,0,0,0,1,0,0,0)
I want to unify them:
(1,"ENV - Enviado",2020,0,0,0,0,0,0,0,0,0,0,3,0)
(4,"CAN - Cancelado",2020,0,0,0,0,0,0,0,3,0,0,0,0)
(7,"FIN - Finalizado",2020,0,0,0,0,0,0,0,0,4,0,0,0)
Thanks in advance!