Tenho no meu banco de dados uma coluna que me diz que minhas requisições foram aprovadas e reprovados.
Preciso de uma query que me fale o total de requisições por funcionário e por tipo de captação(se foi balcão, whatsap,...), o total de aprovados e de reprovados.
SELECT a1.cdcaptacao, fu.nomefun, ttl, aprov, reprov
FROM
fc15100 a, fc08000 fu, fc15000 a1
left join
(SELECT ac1.cdcaptacao as cdcap, fff.nomefun as nmfn, count(ac1.nrorc) as ttl
FROM fc15100 accc, fc15000 ac1
inner join fc08000 fff on accc.cdfunre = fff.cdfun and accc.cdconre = fff.cdcon
WHERE accc.dtentr between '2020-06-01' and '2020-06-30'
and accc.cdfil = '1' and accc.cdconre = '1'
and accc.cdfil = ac1.cdfil and accc.nrorc = ac1.nrorc
group by ac1.cdcaptacao, fff.nomefun
order by fff.nomefun) as total on a1.cdcaptacao = total.cdcap and fu.nomefun = total.nmfn
left join
(SELECT a3.cdcaptacao as cdcap, fff.nomefun as nmfn, count(a3.nrorc) as aprov
FROM fc15100 ac, fc15000 a3
inner join fc08000 fff on ac.cdfunre = fff.cdfun and ac.cdconre = fff.cdcon
WHERE ac.dtentr between '2020-06-01' and '2020-06-30'
and ac.cdfil = '1' and ac.cdconre = '1'
and ac.cdfil = a3.cdfil and ac.nrorc = a3.nrorc
and ac.qtaprov = '1'
group by a3.cdcaptacao, fff.nomefun
order by fff.nomefun) as aprovado on a1.cdcaptacao = aprovado.cdcap and fu.nomefun = aprovado.nmfn
left join
(SELECT a2.cdcaptacao as cdcapt, ff.nomefun as nmfun, count(a2.nrorc) as reprov
FROM fc15100 ab, fc15000 a2
inner join fc08000 ff on ab.cdfunre = ff.cdfun and ab.cdconre = ff.cdcon
WHERE ab.dtentr between '2020-06-01' and '2020-06-30'
and ab.cdfil = '1' and ab.cdconre = '1'
and a2.cdfil = ab.cdfil and a2.nrorc = ab.nrorc
and ab.qtaprov = '0'
group by a2.cdcaptacao, ff.nomefun) as reprovado on a1.cdcaptacao = reprovado.cdcapt and fu.nomefun = reprovado.nmfun
where a1.cdfil = a.cdfil and a1.nrorc = a.nrorc and a.dtentr between '2020-06-01' and '2020-06-05'
and a.cdfil = '1' and a.cdconre = '1'
and a.cdfunre = fu.cdfun and a.cdconre = fu.cdcon
group by a1.cdcaptacao, fu.nomefun, ttl, aprov, reprov
order by fu.nomefun
query qtd total, aprovados e reprovados
em PostgreSQL
Postado
Bom dia!
Tenho no meu banco de dados uma coluna que me diz que minhas requisições foram aprovadas e reprovados.
Preciso de uma query que me fale o total de requisições por funcionário e por tipo de captação(se foi balcão, whatsap,...), o total de aprovados e de reprovados.
ex.: |tpcaptação|funcionario|total|aprovados|reprovados|
fiz desta forma a baixo só que entra em loop.
SELECT a1.cdcaptacao, fu.nomefun, ttl, aprov, reprov
FROM
fc15100 a, fc08000 fu, fc15000 a1
left join
(SELECT ac1.cdcaptacao as cdcap, fff.nomefun as nmfn, count(ac1.nrorc) as ttl
FROM fc15100 accc, fc15000 ac1
inner join fc08000 fff on accc.cdfunre = fff.cdfun and accc.cdconre = fff.cdcon
WHERE accc.dtentr between '2020-06-01' and '2020-06-30'
and accc.cdfil = '1' and accc.cdconre = '1'
and accc.cdfil = ac1.cdfil and accc.nrorc = ac1.nrorc
group by ac1.cdcaptacao, fff.nomefun
order by fff.nomefun) as total on a1.cdcaptacao = total.cdcap and fu.nomefun = total.nmfn
left join
(SELECT a3.cdcaptacao as cdcap, fff.nomefun as nmfn, count(a3.nrorc) as aprov
FROM fc15100 ac, fc15000 a3
inner join fc08000 fff on ac.cdfunre = fff.cdfun and ac.cdconre = fff.cdcon
WHERE ac.dtentr between '2020-06-01' and '2020-06-30'
and ac.cdfil = '1' and ac.cdconre = '1'
and ac.cdfil = a3.cdfil and ac.nrorc = a3.nrorc
and ac.qtaprov = '1'
group by a3.cdcaptacao, fff.nomefun
order by fff.nomefun) as aprovado on a1.cdcaptacao = aprovado.cdcap and fu.nomefun = aprovado.nmfn
left join
(SELECT a2.cdcaptacao as cdcapt, ff.nomefun as nmfun, count(a2.nrorc) as reprov
FROM fc15100 ab, fc15000 a2
inner join fc08000 ff on ab.cdfunre = ff.cdfun and ab.cdconre = ff.cdcon
WHERE ab.dtentr between '2020-06-01' and '2020-06-30'
and ab.cdfil = '1' and ab.cdconre = '1'
and a2.cdfil = ab.cdfil and a2.nrorc = ab.nrorc
and ab.qtaprov = '0'
group by a2.cdcaptacao, ff.nomefun) as reprovado on a1.cdcaptacao = reprovado.cdcapt and fu.nomefun = reprovado.nmfun
where a1.cdfil = a.cdfil and a1.nrorc = a.nrorc and a.dtentr between '2020-06-01' and '2020-06-05'
and a.cdfil = '1' and a.cdconre = '1'
and a.cdfunre = fu.cdfun and a.cdconre = fu.cdcon
group by a1.cdcaptacao, fu.nomefun, ttl, aprov, reprov
order by fu.nomefun
aguardo resposta
e grato desde já