relatorio-dme-dinheiro.sql

select i.documento, TO_CHAR(p.registro, 'MM-YYYY') mes, sum(pf.valor) total, count(p.id) protocolos
from protocolo p 
join protocolo_financeiro pf on p.id = pf.protocolo_id
join indicador_pessoal_versao i on i.id = p.tomador_id or i.id = p.interessado_id  or i.id = p.solicitante_id 
where p.registro::date between '2021-01-01' and '2021-02-28' and i.documento is not null
and pf.forma_pagamento = 'DINHEIRO'
group by 1,2
having sum(pf.valor) >= 30000
order by 1,2 

---------------------------------------------------------------------------------

select i.documento, sum(pf.valor)
from protocolo_financeiro pf 
join protocolo p on p.id = pf.protocolo_id 
join indicador_pessoal_versao i on i.id = p.tomador_id 
where 
	pf.forma_pagamento = 'DINHEIRO' 
AND pf.movimento = 'ENTRADA' 
AND pf.competencia between '2020-04-20' and '2020-04-30'
group by i.documento
having sum(pf.valor) >= 30000

-----------------------------------------------------------------------------------

select i.documento, TO_CHAR(p.registro, 'MM-YYYY') mes, sum(pf.valor) total, count(p.id) protocolos, string_agg(p.codigo::text, ', ') protocolos
from protocolo p 
join protocolo_financeiro pf on p.id = pf.protocolo_id
join indicador_pessoal_versao i on i.id = p.tomador_id or i.id = p.interessado_id  or i.id = p.solicitante_id 
where p.registro::date between '2021-03-01' and '2021-03-31' and i.documento is not null
and pf.forma_pagamento = 'DINHEIRO'
group by 1,2
having sum(pf.valor) >= 30000
order by 1,2 

select
i.documento, p.codigo, p.dominio, pf.movimento, pf.competencia, pf.valor
from protocolo_financeiro pf
join protocolo p on p.id = pf.protocolo_id
join indicador_pessoal_versao i on i.id = p.tomador_id
where
pf.forma_pagamento = 'DINHEIRO'
AND pf.competencia between '2020-05-01' and '2020-05-30'
and i.documento in (
select
i.documento
from protocolo_financeiro pf
join protocolo p on p.id = pf.protocolo_id
join indicador_pessoal_versao i on i.id = p.tomador_id
where
pf.forma_pagamento = 'DINHEIRO'
AND pf.movimento = 'ENTRADA'
AND pf.competencia between '2020-05-01' and '2020-05-30'
group by i.documento
having sum(pf.valor) >= 30000
)
order by i.documento asc, pf.competencia ASC