Antiguedad resumido

[former_member228470]

Hola a Todos

Necesito de la ayuda de ustedes.  Estoy realizando un script para traer los movimientos del cliente, como si fuera el informe de antiguedad, pero resumido.  Es decir, que no se vea el detalle de las facturas ni notas de crédito ni pagos, si no el total resultante de todos lso movimientos.

De la manera como lo hice, utilizando union all, me trae por separada las lineas.

SELECT T0.CardCode AS 'Cliente', T0.CardName AS 'Nombre', T4.City AS 'País',

'Saldo Pendiente'= (DocTotal-PaidToDate) ,

'1-30'  = (CASE WHEN ( DATEDIFF(DAY, docduedate, GETDATE())BETWEEN 0 AND 30) THEN (T0.DocTotal-T0.PaidToDate) ELSE (CASE WHEN DATEDIFF(DAY, T0.docduedate, GETDATE())BETWEEN 1 AND 30 AND T0.Doccur='USD' THEN (T0.DocTotalFC-T0.PaidFC) ELSE 0 END) END) ,

'31-60' = (CASE WHEN (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 THEN (DocTotalFC-PaidFc) ELSE 0 END) END),

'61-90' = (CASE WHEN (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 THEN (DocTotalFC-PaidFC) ELSE 0 END) END),

'90+'   = (CASE WHEN (DATEDIFF(DAY, docduedate, GETDATE())>=90) THEN (T0.DocTotal-T0.PaidToDate) ELSE (CASE WHEN (DATEDIFF(DAY, docduedate, GETDATE())>=90) THEN (T0.DocTotalFC-T0.PaidFC) ELSE 0 END) END)

FROM OINV T0

INNER JOIN OCRD T1 ON T0.CARDCODE = T1.CARDCODE and T1.CardType = 'C'

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

INNER JOIN OSLP T3 ON T0.SlpCode  = T3.SlpCode

INNER JOIN CRD1 T4 on T4.CardCode = T0.CardCode AND T4.AdresType = 'B'

WHERE (T0.DocTotal-T0.PaidToDate > 0 OR DocTotalFC-PaidFC > 0)

UNION ALL

SELECT T0.CardCode AS 'Cliente', T0.CardName AS 'Nombre', T4.City AS 'País',

'Saldo' = (T0.NoDocSum * -1)  ,

'1-30'  = (CASE WHEN (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 THEN (T0.NoDocSumFC * -1) ELSE 0 END) END) ,

'31-60' = (CASE WHEN (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 THEN (T0.NoDocSumFC * -1) ELSE 0 END) END) ,

'61-90' = (CASE WHEN (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 THEN (T0.NoDocSumFC * -1) ELSE 0 END) END),

'90+'   = (CASE WHEN (DATEDIFF(DAY, docduedate, GETDATE())>=90) THEN (T0.NoDocSum * -1) ELSE (CASE WHEN (DATEDIFF(DAY, docduedate, GETDATE())>=90) THEN (T0.NoDocSumFC * -1) ELSE 0 END) END)

FROM ORCT T0

INNER JOIN OCRD T1 ON T0.CARDCODE= T1.CARDCODE and T1.CardType = 'C'

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

left JOIN RCT2 T3 on t0.DocEntry = t3.DocNum

INNER JOIN CRD1 T4 on T4.CardCode = T0.CardCode --AND T4.AdresType = 'B'

WHERE T0.NoDocSum > 0AND T0.[DocType] = 's' AND T0.OpenBal > 0 AND

T0.[JrnlMemo] NOT LIKE '%%Cancelado%%'

GROUP BY T0.DocNum, T0.CardCode, T0.CardName, T4.City, T0.DocDate, T0.DocdueDate, T0.DocCurr, T0.DocTotal,

T0.DocTotalFC, T2.PymntGroup, T0.NoDocSum, T0.NoDocSumFC

UNION ALL

SELECT T0.CardCode AS 'Cliente', T0.CardName AS 'Nombre', T4.City AS 'País',

'Saldo Pendiente'= (DocTotal-PaidToDate) ,

'1-30'  = (CASE WHEN ( DATEDIFF(DAY, docduedate, GETDATE())BETWEEN 0 AND 30) THEN (T0.DocTotal-T0.PaidToDate)* -1 ELSE (CASE WHEN DATEDIFF(DAY, T0.docduedate, GETDATE())BETWEEN 1 AND 30 AND T0.Doccur='USD' THEN (T0.DocTotalFC-T0.PaidFC)* -1 ELSE 0 END) END) ,

'31-60' = (CASE WHEN (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 THEN (DocTotalFC-PaidFc)* -1 ELSE 0 END) END),

'61-90' = (CASE WHEN (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 THEN (DocTotalFC-PaidFC)* -1 ELSE 0 END) END),

'90+'   = (CASE WHEN (DATEDIFF(DAY, docduedate, GETDATE())>=90) THEN (T0.DocTotal-T0.PaidToDate)* -1 ELSE (CASE WHEN (DATEDIFF(DAY, docduedate, GETDATE())>=90) THEN (T0.DocTotalFC-T0.PaidFC)* -1 ELSE 0 END) END)

FROM ORIN T0

INNER JOIN OCRD T1 ON T0.CARDCODE = T1.CARDCODE and T1.CardType = 'S'

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

INNER JOIN OSLP T3 ON T0.SlpCode  = T3.SlpCode

INNER JOIN CRD1 T4 on T4.CardCode = T0.CardCode AND T4.AdresType = 'B'

WHERE (T0.DocTotal-T0.PaidToDate > 0 OR DocTotalFC-PaidFC > 0)

[former_member413321]

Por ser varios Union la manera mas sencilla es crear una tabla temporal con el resultado de tu consulta, y luego devolverlo sumarizado.

La tabla se crea de esta manera

Create table #NombreTabla (

Cliente nvarchar(30),

Nombre nvarchar(300),

Pais nvarchar(300),

Saldo_Pendiente Numeric(19,3),

Primer Numeric(19.3).

Segundo Numeric(19,3),

Tercero Numeric(19,3),

Cuarto Numeric(19,3)

)

Al inicio de tu select Colocas la instruccion Insert #NombreTabla

Con esto todos los registros devueltos quedan en la tabla temporal.

Entonces al final del procedimiento haces el siguiente select.

Select Cliente, Nombre, Pais, Sum(Saldo_Pendiente), Sum(Primer), Sum(Segundo), Sum(Tercero), Sum(Cuarto)

From #NombreTabla

Group by Cliente, Nombre, Pais.

Con esto te da el resultado esperado.

Ahi le colocas bonitos los nombres y le puede poner alias a la consulta final,