Como trazer o VALOR_NAOPAGO > 0

SQL

MySQL

Oracle

Firebird

18/11/2021

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
Marcos Batista

Marcos Batista

Curtidas 0

Melhor post

Emerson Nascimento

Emerson Nascimento

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
GOSTEI 1
POSTAR