Boa tarde pessoal!
Preciso substituir o NULL por 0 ao executar a SQL. Teria que incluir o COALESCE juntamente com o sum e cast?
sum(cast(s2.valorbaixa as money)) as Baixas,
sum(cast(s2.valorop1 as money)) as Glosas,
sum(cast(s2.valorjuros as money)) as 'Juros Pagos',
Gostaria de ajuda!
Obrigado
declare @filial int ='3',
@data date ='2017-10-31 00:00:00.000',
@clifor int='000116'
with
S1 as (
select
a.CODFILIAL,
a.codcfo,
a.idlan,
a.numerodocumento,
a.DATAEMISSAO,
a.VALORORIGINAL
from flan as A
where a.codcfo=@clifor and a.statuslan <> 2 and a.DATAEMISSAO <= @data and a.CODFILIAL=@filial),
S2 as (
select
a.codfilial,
a.idlan,
a.numcontabilbx,
a.databaixa,
a.valorbaixa,
a.valorop1,
a.valorjuros
from flanbaixa as A
where a.status <> 1 and a.databaixa <=@data)
select
s1.codfilial as Filial,
s1.codcfo,
s1.idlan,
s1.dataemissao as Emissão,
s1.numerodocumento as Documento,
cast(s1.valororiginal as money) as Original,
sum(cast(s2.valorbaixa as money)) as Baixas,
sum(cast(s2.valorop1 as money)) as Glosas,
sum(cast(s2.valorjuros as money)) as 'Juros Pagos',
sum(s1.valororiginal-s2.valorbaixa-s2.valorop1+s2.valorjuros) as 'Saldo'
from S1
left join S2
on s1.idlan=s2.idlan
group by
s1.codfilial,
s1.codcfo,
s1.idlan,
s1.dataemissao,
s1.numerodocumento,
s1.valororiginal
