on 09-20-2012 4:07 PM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
User | Count |
---|---|
85 | |
7 | |
6 | |
4 | |
3 | |
3 | |
3 | |
3 | |
3 | |
2 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.