Hola nuevamente. Ya conseguí la tabla del campo en cuestión. Pero el valore del exchange rate no consigo la forma de insertarlo a mi consulta. Es el campo Rate que proviene de la tabla ORTT.
Ahora el detalle es que estoy tratando de colocarlo en esta consulta
select T1.CardCode AS Codigo,
Convert(char(40),T1.cardname) AS Nombre,
case T0.transtype
when '13' then 'Factura de Deudores'
when '14' then 'Nota Credito'
when '24' then 'Pagos'
when '30' then 'Asientos Contables'
else 'Otro'
end 'Tipo Trans',
T0.Ref1 AS Referencia,
T2.TransCode AS 'Codigo tRANS',
T0.transtype,
RIGHT(YEAR(T0.RefDate), 4) AS 'Año',
Case
when DATEPART(MM, T0.RefDate)='1' then '01'
when DATEPART(MM, T0.RefDate)='2' then '02'
when DATEPART(MM, T0.RefDate)='3' then '03'
when DATEPART(MM, T0.RefDate)='4' then '04'
when DATEPART(MM, T0.RefDate)='5' then '05'
when DATEPART(MM, T0.RefDate)='6' then '06'
when DATEPART(MM, T0.RefDate)='7' then '07'
when DATEPART(MM, T0.RefDate)='8' then '08'
when DATEPART(MM, T0.RefDate)='9' then '09'
when DATEPART(MM, T0.RefDate)='10' then '10'
when DATEPART(MM, T0.RefDate)='11' then '11'
when DATEPART(MM, T0.RefDate)='12' then '12' END Mes,
CONVERT(VARCHAR(10), T0.RefDate, 103) AS 'Fecha Docto',
CONVERT(VARCHAR(10), T0.DueDate, 103) 'Vence Docto',
DATEDIFF(day,T0.refdate,T0.duedate) as 'Dias',
SUM(BalDueDeb-BalDueCred) "Saldo L",
SUM(BalScDeb-BalScCred) "Saldo E",
T3.Rate, --->Dato que estoy tratando de insertar
COALESCE((CASE
when (DATEDIFF(dd,T0.RefDate,current_timestamp))+1 < 31
then
case
when syscred <> 0 then isnull(-BALDUECRED,0)
else isnull(sysdeb,0)
end
end),0) '0-30 Dias',
------
COALESCE((CASE
when (DATEDIFF(dd,T0.RefDate,current_timestamp))+1 < 31 and DATEPART(m, T0.RefDate) = DATEPART(m, DATEADD(m, -1, getdate()))--(DATEDIFF(mm,T0.RefDate,current_timestamp))-1<(DATEPART(MM, T0.RefDate))
then
case
when syscred <> 0 then isnull(-BALDUECRED,0)
else isnull(sysdeb,0)
end
end),0) 'Mes Ant 0-30',
------
COALESCE((case when ((datediff(dd,t0.RefDate,current_timestamp))+1 > 30
and (datediff(dd,t0.RefDate,current_timestamp))+1< 61)
then
case
when syscred <> 0 then isnull(-BALDUECRED,0)
else isnull(sysdeb,0)
end
end),0) '31 to 60 Dias',
-----
COALESCE((case when ((datediff(dd,t0.RefDate,current_timestamp))+1 > 30 --AND DATEPART(m, T0.RefDate) = DATEPART(m, DATEADD(m, -1, getdate())) and DATEPART(yyyy,T0.RefDate) = DATEPART(yyyy, DATEADD(yyyy, -1, getdate()))
and (datediff(dd,t0.RefDate,current_timestamp))+1< 61) AND MONTH(T0.RefDate) = (MONTH(getdate())-1) --AND year(T0.refdate) = (year(getdate())-1)--DATEPART(m, T0.RefDate) = DATEPART(m, DATEADD(m, -1, getdate())) and DATEPART(yyyy,T0.RefDate) = DATEPART(yyyy, DATEADD(yyyy, -1, getdate()))
then
case
when syscred <> 0 then isnull(-BALDUECRED,0)
else isnull(sysdeb,0)
end
end),0) 'Mes Ant 31-60',
COALESCE((case when ((datediff(dd,t0.RefDate,current_timestamp))+1 > 60
and (datediff(dd,t0.RefDate,current_timestamp))+1< 91)
then
case
when syscred <> 0 then isnull(-BALDUECRED,0)
else isnull(sysdeb,0)
end
end),0) '61 to 90 Dias',
--
COALESCE((case when ((datediff(dd,t0.RefDate,current_timestamp))+1 > 60
and (datediff(dd,t0.RefDate,current_timestamp))+1< 91) AND MONTH(T0.RefDate) = (MONTH(getdate())-1)
then
case
when syscred <> 0 then isnull(-BALDUECRED,0)
else isnull(sysdeb,0)
end
end),0) 'Mes Ant 61-90',
--
COALESCE((CASE
when (DATEDIFF(dd,t0.RefDate,current_timestamp))+1 > 90
then
case
when syscred= 0 then isnull(sysdeb,0)
when sysdeb= 0 then isnull(-BALDUECRED,0)
end
end),0) '90 + Dias',
--
COALESCE((CASE
when (DATEDIFF(dd,t0.RefDate,current_timestamp))+1 > 90 AND MONTH(T0.RefDate) = (MONTH(getdate())-1)
then
case
when syscred= 0 then isnull(sysdeb,0)
when sysdeb= 0 then isnull(-BALDUECRED,0)
end
end),0) 'Mes Ant 90 + Dias'
from JDT1 T0
INNER JOIN OCRD T1 ON T0.shortname = T1.cardcode and T1.cardtype = 'C'
inner join ojdt t2 on t0.transid=t2.transid where T0.intrnmatch = '0' and T1.CardCode='C123456789' and T0.BALDUEDEB != T0.BALDUECRED
INNER JOIN ORTT T3 ON T2.TransCurr=T3.Currency
Group by T1.CardCode, T1.cardname, T1.Phone1, T0.transtype, T0.Ref1, T2.TransCode, T0.refdate,T0.duedate, T0.SYSCred, T0.BalDueCred, T0.SYSDeb
Pero me da error de sintaxis cuando trato de unir esas tablas para que me muestre ese valor que tanto me hace falta.
Si pudiesen darme una mano se los agradezco inmensamente.
Saludos.