Hola, mi primer aporte es una consulta la cual permite conocer cuales Ordenes de venta terminaron en buen termino y cuales están en proceso, el propósito es conocer cuanto se a vendido a determinado tiempo, espero a alguien le pueda ayudar.
SELECT DISTINCT T0.DocNum, T0.DocDate,T0.CardCode, t0.CardName, T0.DocTotal-T0.VatSum AS [DocTotal]
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
INNER JOIN OINV T4 ON T4.DocEntry = T3.TrgetEntry
INNER JOIN INV1 T5 ON T5.DocEntry = T4.DocEntry
WHERE T5.TargetType <> 14 AND T3.TargetType <> 16 AND T1.TargetType <> 13 AND t0.DocStatus='C' and T4.CANCELED='N' AND T2.DocStatus='C'
AND T0.DocDate >= [%0] AND T0.DocDate <= [%1]
UNION ALL
SELECT DISTINCT T0.DocNum, T0.DocDate,T0.CardCode, t0.CardName, T0.DocTotal-T0.VatSum AS [DocTotal]
FROM ORDR T0 INNER JOIN RDR1 T1 ON T0.[DocEntry] = T1.[DocEntry]
INNER JOIN INV1 T2 ON T2.Baseref = T0.DocEntry AND T2.Basetype = T1.objtype
INNER JOIN OINV T3 ON T3.DocEntry = T2.DocEntry
WHERE T0.DocDate >= [%0] AND T0.DocDate <= [%1] AND T0.CANCELED = 'N' AND T3.CANCELED='N'
AND NOT EXISTS (SELECT * FROM RIN1 T4 WHERE T4.BaseEntry= T2.DocEntry and T4.BaseLine = T2. LineNum) and T0.Docstatus = 'C'
UNION ALL
SELECT DISTINCT T0.DocNum, T0.DocDate,T0.CardCode, t0.CardName, T0.DocTotal-T0.VatSum AS [DocTotal]
FROM ORDR T0 INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry
WHERE T0.DocStatus='O' and T0.DocDate >= [%0] AND T0.DocDate <= [%1]
and NOT EXISTS(SELECT * FROM DLN1 T2 WHERE T2.BaseEntry= T1.DocEntry AND T2.BaseLine= T1.LineNum )
UNION ALL
SELECT DISTINCT T0.DocNum, T0.DocDate,T0.CardCode, t0.CardName, T0.DocTotal-T0.VatSum AS [DocTotal]
FROM ORDR T0 INNER JOIN RDR1 T1 ON T0.[DocEntry] = T1.[DocEntry]
INNER JOIN DLN1 T2 ON T2.BaseEntry = T1.DocEntry AND T2.BaseLine = T1.LineNum INNER JOIN ODLN T3 ON T3.DocEntry = T2.DocEntry
WHERE T0.DocDate >= [%0] AND T0.DocDate <= [%1] AND T0.CANCELED = 'N' and T3.DocStatus='O' AND NOT EXISTS(SELECT * FROM INV1 T4 WHERE T4.BaseEntry= T2.DocEntry AND T4.BaseLine= T2.LineNum)
UNION ALL
SELECT DISTINCT T0.DocNum, T0.DocDate,T0.CardCode, t0.CardName, T0.DocTotal-T0.VatSum AS [DocTotal]
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
INNER JOIN OINV T4 ON T4.DocEntry = T3.TrgetEntry
INNER JOIN INV1 T5 ON T5.DocEntry = T4.DocEntry
WHERE T0.DocDate >= [%0] AND T0.DocDate <= [%1] and T0.DocStatus= 'O' AND T4.DocStatus='O'
