cancel
Showing results for 
Search instead for 
Did you mean: 

Ayuda en query

Former Member
0 Kudos

hola a todos del foro!!!

tengo problemas con el siguiente query

SELECT

T0.[CardCode],

T0.[CardName],

T0.[Balance],

T0.[CreditLine],

T1.[SlpName],

T2.[Name],

T0.[U_Fechaestado],

Case when datediff(dd,T3.DocDueDate,getdate()) > 20 then T3.[DocTotal]-T3.[PaidToDate] else 0 end

FROM OCRD T0  FULL JOIN

     OSLP T1 ON T0.SlpCode = T1.SlpCode FULL JOIN

[dbo].[@ESTADODECARTERA]  T2 ON T0.U_Estcartera = T2.Code FULL JOIN

OINV T3 ON T0.CardCode = T3.CardCode FULL JOIN

NNM1 T4 ON T3.Series = T4.Series

WHERE 1=1

AND (T3.[DocTotal]-T3.[PaidToDate])  > 0 

AND datediff(dd,T3.DocDueDate,getdate()) >0

AND T0.[CardType] ='C'

and T0.[frozenFor] ='N'

GROUP BY T0.[CardCode],T0.[CardName],T0.[Balance],

T0.[CreditLine],T1.[SlpName],

T2.[Name],

T0.[U_Fechaestado],T3.DocDueDate,T3.[DocTotal],T3.[PaidToDate]

Pero el problema es que en la parte del query donde dice

Case when datediff(dd,T3.DocDueDate,getdate()) > 20 then T3.[DocTotal]-T3.[PaidToDate] else 0 end

Necesito solo mostrar si cada socio tiene o no facturas sin pagar con mas de 20 dias a partir de la fecha de vencimiento

algo asi como este pero integrandolo al anterior

SELECT count(Case when datediff(dd,T0.DocdueDate,getdate()) > 20

then T0.[DocTotal]-T0.[PaidToDate] else 0 end)

FROM OINV T0

  INNER JOIN NNM1 T2 ON T0.Series = T2.Series

WHERE 1=1

AND (T0.[DocTotal]-T0.[PaidToDate])  > 0 

AND datediff(dd,T0.DocdueDate,getdate()) >0

GROUP BY T0.[CardCode]

Espero me puedan ayudar

Accepted Solutions (1)

Accepted Solutions (1)

felipe_loyolarodriguez
Active Contributor
0 Kudos

Que quieres mostrar en el CASE;

La deuda atrasada

La cantidad de facturas atrasadas

Un SI o un NO si tiene deuda atrasada

Saludos

Felipe

Former Member
0 Kudos

Hola felipe!!! solamente decir si tiene o no alguna factura con retraso de mas de 20 dias y la cantidad de facturas retrasadas

felipe_loyolarodriguez
Active Contributor
0 Kudos

Carito, prueba esto:

SELECT DISTINCT

          T0.[CardCode],  

          T0.[CardName],  

          T0.[Balance],  

          T0.[CreditLine],  

          T1.[SlpName],  

          T2.[Name],  

          T0.[U_Fechaestado], 

          ISNULL((SELECT COUNT(*)

                              FROM OINV A0

                              WHERE A0.CardCode=T0.CardCode AND DATEDIFF(day,A0.DocDueDate, GETDATE())>20),0) AS [FactAtrasadas]

 

 

FROM OCRD T0 

FULL JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode

FULL JOIN [dbo].[@ESTADODECARTERA]  T2 ON T0.U_Estcartera = T2.Code

FULL JOIN OINV T3 ON T0.CardCode = T3.CardCode

FULL JOIN NNM1 T4 ON T3.Series = T4.Series  

 

 

WHERE 1=1

AND (T3.[DocTotal]-T3.[PaidToDate])  > 0 

AND T0.[CardType] ='C'  

and T0.[frozenFor] ='N'  

 

 

GROUP BY T0.[CardCode],T0.[CardName],T0.[Balance],  

T0.[CreditLine],T1.[SlpName],  

T2.[Name],  

T0.[U_Fechaestado],

T3.DocDueDate,T3.[DocTotal],T3.[PaidToDate] 

Saludos

Felipe.

Former Member
0 Kudos

mmm felipe...hice un pequeño ajuste al query y quedo listo

ISNULL((SELECT COUNT(*)  

                              FROM OINV A0  

                              WHERE A0.CardCode=T0.CardCode AND (A0.[DocTotal]-A0.[PaidToDate])  > 0   AND DATEDIFF(day,A0.DocDueDate, GETDATE())>20),0) AS [FactAtrasadas] 

Gracias!!!

Answers (0)