AYUDA QUERY

[Former Member]

BUENAS TARDES

ENCONTRE ESTA QUERY EN EL FORO , ME PARECE MUY INTERESANTE

CON LO UNICO COMO PUEDO HACER PARA LAS FACTURAS QUE TIENEN NOTA DE CREDITO

NO SE LAS TRAIGA

ESTA ES LA CONSULTA

SELECT T0.CARDNAME [NOMBRE CLIENTE],T0.DOCNUM [FACTURA NO],T0.DOCDATE [FECHA FACTURA],T8.SERIES,

T0.DOCTOTAL [VALOR],T6.DocDate  [FECHA PAGO],

T4.DEPOSDATE [FECHA DEPOSITO],

DATEDIFF(DD,T0.DOCDATE,T6.DOCDATE) [DIAS REALIZACION],

DATEDIFF(DD,T6.DOCDATE,T4.DEPOSDATE) [REALIZATION DAYS TO WITH PDC],

DATEDIFF(DD,T0.DOCDATE,T4.DEPOSDATE) [TURNAROUND DAYS]

FROM OINV T0

LEFT JOIN RCT2 T5 ON T0.DocEntry = T5.DocEntry

LEFT JOIN ORCT T6 ON T6.DocEntry=T5.DocNum

left JOIN RCT1 T2 ON T2.DOCNUM=T6.DOCNUM

left join  orin     t8 on  t0.DOCNUM = t8.DOCNUM

left JOIN OCHH T3 ON T3.Checknum=T2.checknum AND T3.CARDCODE=T0.CARDCODE left JOIN ODPS T4 ON T3.DpstAbs=T4.DeposId

WHERE T0.DOCDATE between  [%2] and [%3]

GRACIAS DE ANTEMANO POR LA AYUIDA

[felipe_loyolarodriguez · Accepted solution]

Buenas, pruebala así

SELECT

    T0.CARDNAME [NOMBRE CLIENTE],

    T0.DOCNUM [FACTURA NO],

    T0.DOCDATE [FECHA FACTURA],

    T8.SERIES,

    T0.DOCTOTAL [VALOR],

    T6.DocDate  [FECHA PAGO],

    T4.DEPOSDATE [FECHA DEPOSITO],

    DATEDIFF(DD,T0.DOCDATE,T6.DOCDATE) [DIAS REALIZACION],

    DATEDIFF(DD,T6.DOCDATE,T4.DEPOSDATE) [REALIZATION DAYS TO WITH PDC],

    DATEDIFF(DD,T0.DOCDATE,T4.DEPOSDATE) [TURNAROUND DAYS]

FROM OINV T0

INNER JOIN INV1 T1 ON T1.DocEntry=T0.DocEntry

LEFT JOIN RCT2 T5 ON T0.DocEntry = T5.DocEntry

LEFT JOIN ORCT T6 ON T6.DocEntry=T5.DocNum

LEFT JOIN RCT1 T2 ON T2.DOCNUM=T6.DOCNUM

LEFT JOIN ORIN T8 ON T0.DOCNUM = T8.DOCNUM

LEFT JOIN OCHH T3 ON T3.Checknum=T2.checknum AND T3.CARDCODE=T0.CARDCODE

LEFT JOIN ODPS T4 ON T3.DpstAbs=T4.DeposId

WHERE T0.DOCDATE BETWEEN '[%2]' AND '[%3]' AND T1.TargetType != 14

Saludos

[Former Member · Accepted solution]

Gracias felipe con contestar

fijate que la probe y me trae varias veces la misma facturas

que estara mal !!

[felipe_loyolarodriguez · Accepted solution]

Así debería funcionar

SELECT DISTINCT

    T0.CARDNAME [NOMBRE CLIENTE],

    T0.DOCNUM [FACTURA NO],

    T0.DOCDATE [FECHA FACTURA],

    T8.SERIES,

    T0.DOCTOTAL [VALOR],

    T6.DocDate  [FECHA PAGO],

    T4.DEPOSDATE [FECHA DEPOSITO],

    DATEDIFF(DD,T0.DOCDATE,T6.DOCDATE) [DIAS REALIZACION],

    DATEDIFF(DD,T6.DOCDATE,T4.DEPOSDATE) [REALIZATION DAYS TO WITH PDC],

    DATEDIFF(DD,T0.DOCDATE,T4.DEPOSDATE) [TURNAROUND DAYS]

FROM OINV T0

INNER JOIN INV1 T1 ON T1.DocEntry=T0.DocEntry

LEFT JOIN RCT2 T5 ON T0.DocEntry = T5.DocEntry

LEFT JOIN ORCT T6 ON T6.DocEntry=T5.DocNum

LEFT JOIN RCT1 T2 ON T2.DOCNUM=T6.DOCNUM

LEFT JOIN ORIN T8 ON T0.DOCNUM = T8.DOCNUM

LEFT JOIN OCHH T3 ON T3.Checknum=T2.checknum AND T3.CARDCODE=T0.CARDCODE

LEFT JOIN ODPS T4 ON T3.DpstAbs=T4.DeposId

WHERE T0.DOCDATE BETWEEN '[%2]' AND '[%3]' AND T1.TargetType != 14

ORDER BY T0.DocDate, T0.DocNum

FOR BROWSE

Saludos

[Former Member · Accepted solution]

gracias Felipe , me funciono