Last active
April 27, 2021 20:32
-
-
Save jonathanalves/9ed7b35664ce6c0ef907da153eb13414 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
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