Cómo calcular las ordenes de venta por tiempo

sql
Etiquetas: #<Tag:0x00007f4360d1a998>

#1

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'

#2

@leobardo491, gracias por tu aporte.

Lo habías publicado en la sección de “presentaciones”, y ahí, solo debería haber… presentaciones :wink:
Por favor, noto que tú no tienes justamente el tema de presentación, procura crear uno, así te conocemos mejor, nos cuentas con qué versión de SAP trabajas, hace cuánto, etc.

Saludos