Query Antigüedad de Clientes

[Former Member]

Buenos Días Consultores

Utilice un código que encontré  en el portal para generar la antigüedad de clientes, habra un manera de reconstruir el saldo a la fecha que se le solicite el reporte, anexo. código.

DECLARE @LOCCUR VARCHAR(3)

SET @LOCCUR = '$'

SELECT T0.CardCode AS 'Cod S/N', T0.CardName AS Nombre,T3.CardCode AS 'Cod Barco', T3.CardName AS 'BARCO',

    'TotalDoc'= (case when DocCur='USD' then (T0.DocTotalFC) else (DocTotal) end) , T0.DocNum AS 'N/Doc' ,

    DOCDATE as 'Fech/Crea',T0.DOCDUEDATE as 'Fech/Venc.',

    'Saldo Pendiente'= (case when DocCur='USD' then (T0.DocTotalFC-T0.PaidFC) else (DocTotal-PaidToDate) end)  ,'Corriente' = (case when (Doccur=@LOCCUR and getdate() <= DocDueDate) then (DocTotal-PaidToDate) else (case when getdate() <= DocDueDate and Doccur='USD' then (DocTotalFC-PaidFC) else 0 end) end) ,

    ' 0-30' = (case when (T0.Doccur=@LOCCUR and DATEDIFF(day, T0.docduedate, getdate())BETWEEN 0 and 30) then (T0.DocTotal-T0.PaidToDate) else (case when DATEDIFF(day, T0.docduedate, getdate())BETWEEN 0 AND 30 and T0.Doccur='USD' then (T0.DocTotalFC-T0.PaidFC) else 0 end) end) ,

    ' 31-60' = (case when (DocCur=@LOCCUR and DATEDIFF(day, docduedate, getdate())>=31 and DATEDIFF(day, docduedate, getdate())<=60) then (T0.DocTotal-T0.PaidToDate) else (case when DATEDIFF(day, docduedate, getdate())>=31 and DATEDIFF(day, docduedate , getdate())<=60 and DocCur='USD' then (DocTotalFC-PaidFc)else 0 end) end) ,

    ' 61-90' = (case when (DocCur=@LOCCUR and DATEDIFF(day, docduedate, getdate())>=61 and DATEDIFF(day, docduedate, getdate())<=90) then (T0.DocTotal-T0.PaidToDate) else (case when DATEDIFF(day, docduedate, getdate())>=61 and DATEDIFF(day, docduedate, getdate())<=90 and DocCur='USD' then (DocTotalFC-PaidFC) else 0 end) end),

    '90+' = (case when (DocCur=@LOCCUR and DATEDIFF(day, docduedate, getdate())>=90) then (T0.DocTotal-T0.PaidToDate) else (case when (DocCur='USD' and DATEDIFF(day, docduedate, getdate())>=90) then (T0.DocTotalFC-T0.PaidFC) else 0 end) end),

    'Factura' as 'Docto'

FROM OINV T0

    INNER JOIN OCRD T1 ON T0.CARDCODE= T1.CARDCODE

    INNER JOIN OCTG T2 ON T1.GROUPNUM = T2.GROUPNUM

    LEFT JOIN OCRD T3 ON T0.BPChCode = T3.CardCode

    INNER JOIN OCTG T4 ON T0.GroupNum = T4.GroupNum

WHERE (T0.DocTotal-T0.PaidToDate > 0 or DocTotalFC-PaidFC > 0) and T0.[DocDate] <=[%0]

union all

SELECT T0.CardCode AS Cliente, T0.CardName AS Nombre, T3.CardCode AS 'Cod Barco',T3.CardName AS 'BARCO',

    'TotalDoc'= (case when DocCur='USD' then (T0.DocTotalFC) else (DocTotal) end)*-1, T0.DocNum AS NoDocto,

    DOCDATE as FechaDocto, T0.DOCDUEDATE as Vencimiento,

    'Saldo'= (case when DocCur='USD' then (T0.DocTotalFC-T0.PaidFC)*-1 else (DocTotal-PaidToDate)*-1 end) ,

    'Por Vencer' = (case when (Doccur=@LOCCUR and getdate() <= DocDueDate) then (DocTotal-PaidToDate)*-1 else (case when getdate() <= DocDueDate and Doccur='USD' then (DocTotalFC-PaidFC)*-1 else 0 end) end) ,

    ' 0-30' = (case when (Doccur=@LOCCUR and DATEDIFF(day, docduedate, getdate())>=0 and DATEDIFF (day, docduedate, getdate())<=30) then (T0.DocTotal-T0.PaidToDate)*-1 else (case when DATEDIFF(day, docduedate, getdate())>=0 and DATEDIFF(day, docduedate, getdate())<=30 and Doccur='USD' then (DocTotalFC-PaidFC)*-1 else 0 end) end) ,

    ' 31-60' = (case when (DocCur=@LOCCUR and DATEDIFF(day, docduedate, getdate())>=31 and DATEDIFF(day, docduedate, getdate())<=60) then (T0.DocTotal-T0.PaidToDate)*-1 else (case when DATEDIFF(day, docduedate, getdate())>=31 and DATEDIFF(day, docduedate , getdate())<=60 and DocCur='USD' then (DocTotalFC-PaidFc)*-1 else 0 end) end) ,

    ' 61-90' = (case when (DocCur=@LOCCUR and DATEDIFF(day, docduedate, getdate())>=61 and DATEDIFF(day, docduedate, getdate())<=90) then (T0.DocTotal-T0.PaidToDate)*-1 else (case when DATEDIFF(day, docduedate, getdate())>=61 and DATEDIFF(day, docduedate, getdate())<=90 and DocCur='USD' then (DocTotalFC-PaidFC)*-1 else 0 end) end),

    '90+' = (case when (DocCur=@LOCCUR and DATEDIFF(day, docduedate, getdate())>=90) then (T0.DocTotal-T0.PaidToDate)*-1 else (case when (DocCur='USD' and DATEDIFF(day, docduedate, getdate())>=90) then (T0.DocTotalFC-T0.PaidFC)*-1 else 0 end) end) ,

    'NC' as 'Docto'

FROM ORIN T0

    INNER JOIN OCRD T1 ON T0.CARDCODE= T1.CARDCODE

    INNER JOIN OCTG T2 ON T1.GROUPNUM = T2.GROUPNUM

    LEFT JOIN OCRD T3 ON T0.BPChCode = T3.CardCode

    INNER JOIN OCTG T4 ON T0.GroupNum = T4.GroupNum

WHERE (T0.DocTotal-T0.PaidToDate > 0 or DocTotalFC-PaidFC > 0) and T0.[DocDate] <=[%0]

union all

SELECT T0.CardCode AS Cliente, T0.CardName AS Nombre,NULL, NULL,

    'TotalDoc'= (case when DocCurr='USD' then (T0.DocTotalFC) else (DocTotal) end) , T0.DocNum AS NoDocto,

    DOCDATE as FechaDocto, T0.DOCDUEDATE as Vencimiento,

    'Saldo'= (case when DocCurr='USD' then (T0.NoDocSumFC * -1) else (T0.NoDocSum * -1) end) ,

    'Por Vencer' = (case when (DocCurr=@LOCCUR and getdate() <= DocDueDate) then (T0.NoDocSum) else (case when getdate() <= DocDueDate and DocCurr='USD' then (T0.NoDocSumFC * -1) else 0 end) end) ,

    ' 0-30' = (case when (DocCurr=@LOCCUR and DATEDIFF(day, docduedate, getdate())>=0 and DATEDIFF (day, docduedate, getdate())<=30) then (T0.NoDocSum * -1) else (case when DATEDIFF(day, docduedate, getdate())>=0 and DATEDIFF(day, docduedate, getdate())<=30 and DocCurr='USD' then (T0.NoDocSumFC * -1) else 0 end) end) ,

    ' 31-60' = (case when (DocCurr=@LOCCUR and DATEDIFF(day, docduedate, getdate())>=31 and DATEDIFF(day, docduedate, getdate())<=60) then (T0.NoDocSum * -1) else (case when DATEDIFF(day, docduedate, getdate())>=31 and DATEDIFF(day, docduedate , getdate())<=60 and DocCurr='USD' then (T0.NoDocSumFC * -1)else 0 end) end) ,

    ' 61-90' = (case when (DocCurr=@LOCCUR and DATEDIFF(day, docduedate, getdate())>=61 and DATEDIFF(day, docduedate, getdate())<=90) then (T0.NoDocSum * -1) else (case when DATEDIFF(day, docduedate, getdate())>=61 and DATEDIFF(day, docduedate, getdate())<=90 and DocCurr='USD' then (T0.NoDocSumFC * -1) else 0 end) end),

    '90+' = (case when (DocCurr=@LOCCUR and DATEDIFF(day, docduedate, getdate())>=90) then (T0.NoDocSum * -1) else (case when (DocCurr='USD' and DATEDIFF(day, docduedate, getdate())>=90) then (T0.NoDocSumFC * -1) else 0 end) end),

    'Cobranza' as 'Docto'

FROM ORCT T0

    INNER JOIN OCRD T1 ON T0.CARDCODE= T1.CARDCODE

    INNER JOIN OCTG T2 ON T1.GROUPNUM = T2.GROUPNUM

    WHERE (T0.NoDocSum > 0 or T0.NoDocSumFC > 0) AND T0.[DocType] = 'C' AND T0.OpenBal > 0 AND T0.[JrnlMemo] not like '%%Cancelado%%' and T0.[DocDate] <=[%0]

ORDER BY T0.CardCode, T0.DocDate

Saludos Cordiales

Carlos Ortega.

[felipe_loyolarodriguez]

Buenas noches

Revisa el siguiente post:

Saludos

FLR

[felipe_loyolarodriguez]

Buenas noches

Revisa el siguiente post:

Saludos

FLR