Como trazer o VALOR_NAOPAGO > 0
18/11/2021
0
Como trazer o VALOR_NAOPAGO > 0
select fcctaext.con, tbconven.nome, fcctaext.anopro, fcctaext.paciente,
case
when fcctaext.con in(2,8) then 0
else max(fcctaext.doc)
end as documento,
fcctaext.mespro mespro,
case fcctaext.mespro
when 1 then 'Janeiro'
when 2 then 'Fevereiro'
when 3 then 'Março'
when 4 then 'Abril'
when 5 then 'Maio'
when 6 then 'Junho'
when 7 then 'Julho'
when 8 then 'Agosto'
when 9 then 'Setembro'
when 10 then 'Outubro'
when 11 then 'Novembro'
when 12 then 'Dezembro'
end as mes,
fclanext.valor_c,
fclanext.vlr_pg,
fclanext.vlr_re,
sum (fclanext.valor_c) as valor_faturado,
sum(fclanext.vlr_pg) as valor_pago,
sum(fclanext.vlr_re) as reapresentado,
sum (case
when fclanext.glosado='T' and (fclanext.valor_c - fclanext.vlr_pg)>0
then (fclanext.valor_c - fclanext.vlr_pg) -fclanext.vlr_rp
else 0 end)
as glosa_aceita,
sum (fclanext.vlr_rp)as vlr_reap_pg,
sum (case
when fclanext.glosado='T' and (fclanext.valor_c - fclanext.vlr_pg)>0
then (fclanext.valor_c - fclanext.vlr_pg)
else 0 end)
as valor_glosa,
sum (case
when fclanext.vlr_pg - fclanext.valor_c >0
then fclanext.vlr_pg - fclanext.valor_c
else 0 end)
as pago_maior,
case fclanext.crede
when 'T' then sum (fclanext.vlr_pg)
else 0
end as valor_crede,
sum (case
when fclanext.glosado='T' and (fclanext.valor_c - fclanext.vlr_pg)>0
then (fclanext.valor_c - fclanext.vlr_pg) -fclanext.vlr_re
else 0 end)
as glosa_devida,
sum (case
when fclanext.glosado='F'
then fclanext.valor_c
else 0 end)
as valor_NaoPago
from fclanext
inner join fcctaext on fclanext.id_fcctaext=fcctaext.id
inner join tbconven on fcctaext.con=tbconven.cod
where fcctaext.mespro between :Mes_Inicial and :Mes_Final
and fcctaext.anopro=:anopro
and fcctaext.con between :convinicial and :convfinal
and fcctaext.pacote<>'S'
and fcctaext.fechada='A'
group by fcctaext.con, tbconven.nome, fcctaext.anopro, fclanext.crede, fcctaext.mespro, fclanext.valor_c,
fclanext.vlr_pg,
fclanext.vlr_re,
fcctaext.paciente
order by fcctaext.mespro, fcctaext.con, fcctaext.paciente
select fcctaext.con, tbconven.nome, fcctaext.anopro, fcctaext.paciente,
case
when fcctaext.con in(2,8) then 0
else max(fcctaext.doc)
end as documento,
fcctaext.mespro mespro,
case fcctaext.mespro
when 1 then 'Janeiro'
when 2 then 'Fevereiro'
when 3 then 'Março'
when 4 then 'Abril'
when 5 then 'Maio'
when 6 then 'Junho'
when 7 then 'Julho'
when 8 then 'Agosto'
when 9 then 'Setembro'
when 10 then 'Outubro'
when 11 then 'Novembro'
when 12 then 'Dezembro'
end as mes,
fclanext.valor_c,
fclanext.vlr_pg,
fclanext.vlr_re,
sum (fclanext.valor_c) as valor_faturado,
sum(fclanext.vlr_pg) as valor_pago,
sum(fclanext.vlr_re) as reapresentado,
sum (case
when fclanext.glosado='T' and (fclanext.valor_c - fclanext.vlr_pg)>0
then (fclanext.valor_c - fclanext.vlr_pg) -fclanext.vlr_rp
else 0 end)
as glosa_aceita,
sum (fclanext.vlr_rp)as vlr_reap_pg,
sum (case
when fclanext.glosado='T' and (fclanext.valor_c - fclanext.vlr_pg)>0
then (fclanext.valor_c - fclanext.vlr_pg)
else 0 end)
as valor_glosa,
sum (case
when fclanext.vlr_pg - fclanext.valor_c >0
then fclanext.vlr_pg - fclanext.valor_c
else 0 end)
as pago_maior,
case fclanext.crede
when 'T' then sum (fclanext.vlr_pg)
else 0
end as valor_crede,
sum (case
when fclanext.glosado='T' and (fclanext.valor_c - fclanext.vlr_pg)>0
then (fclanext.valor_c - fclanext.vlr_pg) -fclanext.vlr_re
else 0 end)
as glosa_devida,
sum (case
when fclanext.glosado='F'
then fclanext.valor_c
else 0 end)
as valor_NaoPago
from fclanext
inner join fcctaext on fclanext.id_fcctaext=fcctaext.id
inner join tbconven on fcctaext.con=tbconven.cod
where fcctaext.mespro between :Mes_Inicial and :Mes_Final
and fcctaext.anopro=:anopro
and fcctaext.con between :convinicial and :convfinal
and fcctaext.pacote<>'S'
and fcctaext.fechada='A'
group by fcctaext.con, tbconven.nome, fcctaext.anopro, fclanext.crede, fcctaext.mespro, fclanext.valor_c,
fclanext.vlr_pg,
fclanext.vlr_re,
fcctaext.paciente
order by fcctaext.mespro, fcctaext.con, fcctaext.paciente
Marcos Batista
Curtir tópico
+ 0
Responder
Post mais votado
19/11/2021
tente com having
select fcctaext.con, tbconven.nome, fcctaext.anopro, fcctaext.paciente, case when fcctaext.con in(2,8) then 0 else max(fcctaext.doc) end as documento, fcctaext.mespro mespro, case fcctaext.mespro when 1 then 'Janeiro' when 2 then 'Fevereiro' when 3 then 'Março' when 4 then 'Abril' when 5 then 'Maio' when 6 then 'Junho' when 7 then 'Julho' when 8 then 'Agosto' when 9 then 'Setembro' when 10 then 'Outubro' when 11 then 'Novembro' when 12 then 'Dezembro' end as mes, fclanext.valor_c, fclanext.vlr_pg, fclanext.vlr_re, sum(fclanext.valor_c) as valor_faturado, sum(fclanext.vlr_pg) as valor_pago, sum(fclanext.vlr_re) as reapresentado, sum(case when fclanext.glosado='T' and (fclanext.valor_c - fclanext.vlr_pg)>0 then (fclanext.valor_c - fclanext.vlr_pg) -fclanext.vlr_rp else 0 end) as glosa_aceita, sum(fclanext.vlr_rp)as vlr_reap_pg, sum(case when fclanext.glosado='T' and (fclanext.valor_c - fclanext.vlr_pg)>0 then (fclanext.valor_c - fclanext.vlr_pg) else 0 end) as valor_glosa, sum(case when fclanext.vlr_pg - fclanext.valor_c >0 then fclanext.vlr_pg - fclanext.valor_c else 0 end) as pago_maior, sum(case when fclanext.crede = 'T' then fclanext.vlr_pg else 0 end) as valor_crede, sum(case when fclanext.glosado ='T' and (fclanext.valor_c - fclanext.vlr_pg) > 0 then (fclanext.valor_c - fclanext.vlr_pg) - fclanext.vlr_re else 0 end) as glosa_devida, sum(case when fclanext.glosado = 'F' then fclanext.valor_c else 0 end) as valor_NaoPago from fclanext inner join fcctaext on fclanext.id_fcctaext=fcctaext.id inner join tbconven on fcctaext.con=tbconven.cod where fcctaext.mespro between :Mes_Inicial and :Mes_Final and fcctaext.anopro = :anopro and fcctaext.con between :convinicial and :convfinal and fcctaext.pacote <> 'S' and fcctaext.fechada = 'A' group by fcctaext.con, tbconven.nome, fcctaext.anopro, fclanext.crede, fcctaext.mespro, fclanext.valor_c, fclanext.vlr_pg, fclanext.vlr_re, fcctaext.paciente having sum(case when fclanext.glosado='F' then fclanext.valor_c else 0 end) > 0 -- valor não pago order by fcctaext.mespro, fcctaext.con, fcctaext.paciente
Emerson Nascimento
Responder
Clique aqui para fazer login e interagir na Comunidade :)