Ordenes de venta sin factura

[Elizabeth]

Ordenes de venta con orden de entrega o factura?
Estoy haciendo una consulta, la necesito tal cual la preguntan aquí, pero también quiero agregar todas las ordenes de venta que no tienen como puedo agregar esta parte?

[grossney]

Hola Elizabeth…
Basado en la solución del tema que mencionas, creo que deberías agregar un tercer sql en el UNION, con lo siguiente:

SELECT DISTINCT T0.[DocNum], T0.[DocDate], T0.[CardName], NULL , NULL, NULL , NULL
FROM ORDR T0
WHERE T0.[DocDate] >=[%0] AND T0.[DocDate] <=[%1]
AND NOT EXISTS (SELECT 1 FROM DLN1 T1 WHERE T1.[BaseEntry] = T0.[DocEntry] AND T1.[BaseType] = T0.[ObjType])
AND NOT EXISTS (SELECT 1 FROM INV1 T3 WHERE T3.[BaseEntry] = T0.[DocEntry] AND T3.[BaseType] = T0.[ObjType])

completo, quedaría así:

SELECT DISTINCT T0.[DocNum], T0.[DocDate], T0.[CardName], T2.[DocNum] AS NumeroEntrega, T2.[DocDate] AS FechaEntrega, T4.[DocNum], T4.[DocDate]
FROM ORDR T0
INNER JOIN DLN1 T1 ON T1.[BaseEntry] = T0.[DocEntry] AND T1.[BaseType] = T0.[ObjType]
INNER JOIN ODLN T2 ON T1.[DocEntry] = T2.[DocEntry]
LEFT JOIN INV1 T3 ON T3.[BaseEntry] = T2.[DocEntry] AND T3.[BaseType] = T2.[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],NULL AS NumeroEntrega, NULL AS FechaEntrega, T4.[DocNum], T4.[DocDate]
FROM ORDR T0
INNER JOIN INV1 T3 ON T3.[BaseEntry] = T0.[DocEntry] AND T3.[BaseType] = T0.[ObjType]
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], T0.[CardName], NULL , NULL, NULL , NULL
FROM ORDR T0
WHERE T0.[DocDate] >=[%0] AND T0.[DocDate] <=[%1]
AND NOT EXISTS (SELECT 1 FROM DLN1 T1 WHERE T1.[BaseEntry] = T0.[DocEntry] AND T1.[BaseType] = T0.[ObjType])
AND NOT EXISTS (SELECT 1 FROM INV1 T3 WHERE T3.[BaseEntry] = T0.[DocEntry] AND T3.[BaseType] = T0.[ObjType])

Saludos