Hola, logré realizar el siguiente query
SELECT DISTINCT T0.[DocNum], T0.[DocDate] AS ‘Fecha de Pedido’, T0.[CardName], T2.[DocNum] AS ‘N de NE’, T2.[DocDate] AS ‘Fecha de NE’, T4.[DocNum] AS ‘N de factura’, T4.[DocDate] AS ‘Fecha de factura’, T4.[DocTotal], T4.[PaidSum] AS ‘Total saldado’, NULL AS NdeDevolucion, NULL AS FechaDevol FROM ORDR T0
INNER JOIN DLN1 T1 ON T1.[BaseRef] = T0.[DocNum]
INNER JOIN ODLN T2 ON T1.[DocEntry] = T2.[DocEntry]
INNER JOIN INV1 T3 ON T3.[BaseRef] = T2.[DocNum]
INNER JOIN OINV T4 ON T3.[DocEntry] = T4.[DocEntry]
WHERE T0.[DocDate] >=[%0] AND T0.[DocDate] <=[%1]
UNION ALL
SELECT DISTINCT T0.[DocNum], T0.[DocDate] AS ‘Fecha de Pedido’, T0.[CardName],NULL AS NumeroEntrega, NULL AS FechaEntrega, T4.[DocNum] AS ‘N de factura’, T4.[DocDate] AS ‘Fecha de factura’, T4.[DocTotal], T4.[PaidSum] AS ‘Total saldado’, NULL AS NdeDevolucion, NULL AS FechaDevol
FROM ORDR T0
LEFT JOIN INV1 T3 ON T3.[BaseEntry] = T0.[DocEntry] AND T3.[BaseType] = T0.[ObjType]
LEFT JOIN OINV T4 ON T3.[DocEntry] = T4.[DocEntry]
WHERE T0.[DocDate] >=[%0] AND T0.[DocDate] <=[%1]
UNION ALL
SELECT Distinct T0.DocNum,T0.DocDate, T0.CardName, T2.[DocNum] AS ‘N de NE’, T2.[DocDate] AS ‘Fecha de NE’, NULL AS NumeroFactura, NULL AS FechaFactura, NULL AS DocTotal, NULL AS PagoTotal, T6.[DocNum] as ‘N de Devolucion’, T6.[DocDate] FROM ORDR T0
INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN ODLN T2 ON T2.DocEntry = T1.TrgetEntry
INNER JOIN DLN1 T3 on T3.DocEntry = T2.Docentry
LEFT JOIN ORDN T6 ON T6.DocEntry = T3.TrgetEntry
LEFT JOIN RDN1 T7 ON T7.DocEntry = T6.DocEntry
WHERE T0.[DocDate] >=[%0] AND T0.[DocDate] <=[%1]
Tal como quiero el query me relaciona, pedido, NE, factura y devolución en los casos que corresponden, sin embargo, en el caso que se hacen múltiples Notas de entrega NE de un pedido, también este me da como resultado un espacio en blanco sin datos que no se que significa, aun cuando todos los productos que conforman el pedido se encuentran disgregados en las NE
Ejemplo
N Ped | Fecha Pedido | RazonSocial | NE | Fecha NE | N fact | Fecha Factura | Monto | Total Saldado | N DEV | Fecha DEV |
---|---|---|---|---|---|---|---|---|---|---|
9721 | 14/10/2019 | DISTRIBUIDORA | 35449 | 15/10/2019 | 0 | 0 | 237 | 28/10/2019 | ||
9721 | 14/10/2019 | DISTRIBUIDORA | 35449 | 15/10/2019 | 21979 | 15/10/2019 | 2.098.420,80 | 2.098.420,80 | ||
9721 | 14/10/2019 | DISTRIBUIDORA | 35448 | 15/10/2019 | 0 | 0 | 237 | 28/10/2019 | ||
9721 | 14/10/2019 | DISTRIBUIDORA | 35447 | 15/10/2019 | 0 | 0 | 237 | 28/10/2019 | ||
9721 | 14/10/2019 | DISTRIBUIDORA | 35447 | 15/10/2019 | 21979 | 15/10/2019 | 2.098.420,80 | 2.098.420,80 | ||
9721 | 14/10/2019 | DISTRIBUIDORA | 0 | 0 |