espero que les sea util.
select
T1.cardcode 'Codigo',
Convert(char(40),T1.cardname) 'Nombre',
t1.Phone1,
case T0.transtype
when '13' then 'Factura'
when '14' then 'Nota Credito'
when '24' then 'Pagos'
else 'Otro'
end 'Tipo Trans',
Ref1 'Referencia',
CONVERT(VARCHAR(10), RefDate, 103) 'Fecha Docto' ,
CONVERT(VARCHAR(10), duedate, 103) 'Vence Docto',
CASE
when (DATEDIFF(dd,refdate,current_timestamp))+1 < 31
then
case
when syscred <> 0 then isnull(-syscred,0)
else isnull(sysdeb,0)
end
end "0-30 Dias",
case when ((datediff(dd,refdate,current_timestamp))+1 > 30
and (datediff(dd,refdate,current_timestamp))+1< 61)
then
case
when syscred <> 0 then isnull(-syscred,0)
else isnull(sysdeb,0)
end
end "31 to 60 Dias",
case when ((datediff(dd,refdate,current_timestamp))+1 > 60
and (datediff(dd,refdate,current_timestamp))+1< 91)
then
case
when syscred <> 0 then isnull(-syscred,0)
else isnull(sysdeb,0)
end
end "61 to 90 Dias",
CASE
when (DATEDIFF(dd,refdate,current_timestamp))+1 > 90
then
case
when syscred= 0 then isnull(sysdeb,0)
when sysdeb= 0 then isnull(-syscred,0)
end
end "90 + Dias"
from dbo.JDT1 T0 with(nolock)
INNER JOIN
dbo.OCRD T1 with(nolock)
ON T0.shortname = T1.cardcode
and T1.cardtype = 'c'
where T0.intrnmatch = '0'
and T0.BALDUEDEB != T0.BALDUECRED
