Jump to content
Fórum Script Brasil
  • 0

query qtd total, aprovados e reprovados


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
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á




Link to post
Share on other sites

0 answers to this question

Recommended Posts

There have been no answers to this question yet

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Forum Statistics

    • Total Topics
    • Total Posts
  • Create New...