SAP QUERY ANALISIS DE VENTAS

[jpalc-02]

Hello dear good afternoon

I have this sentence but it doesn't come out

Identical to SAP's default sales analysis

esta es la codificacion

SELECT DISTINCT T90."ItemCode", T90."Dscription", SUM(T90."CANTIDAD") as "CANTIDAD" ,SUM(T90."Importe de ventas soles") AS "IMPORTE VENTA SOLES"

FROM

(

SELECT T1."ItemCode", T1."Dscription" , SUM(T1."Quantity") AS "CANTIDAD",T1."DocDate",SUM(T0."DocTotal") AS "Importe de ventas soles"

FROM OINV T0

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

WHERE T0."CANCELED" ='N' THEN T1."Quantity" and t1."DocDate">= '20230801' and t1."DocDate"<= '20230831' AND T0."DocType" = 'I'

--AND WHERE

GROUP BY T1."ItemCode", T1."Dscription",T1."DocDate"

UNION ALL

SELECT T1."ItemCode", T1."Dscription", (-1)* SUM(T1."Quantity") AS "CANTIDAD",T1."DocDate",(-1)*SUM(T0."DocTotal") AS "Importe de ventas soles"

FROM ORIN T0

INNER JOIN RIN1 T1 ON T0."DocEntry" = T1."DocEntry"

WHERE T0."CANCELED" ='N' THEN T1."Quantity" and t0."DocDate">= '20230801' and t0."DocDate"<= '20230831' AND T0."DocType" = 'I'

GROUP BY T1."ItemCode", T1."Dscription",T1."DocDate",T0."DocTotal"

-- ORDER BY SUM(T1."Quantity")

) T90

WHERE T90."ItemCode" IS NOT NULL and T90."DocDate">='20230801' and T90."DocDate"<='20230831'

GROUP BY T90."ItemCode", T90."Dscription"

ORDER BY SUM(T90."CANTIDAD") DESC

How could I make it visualize and be identical to my sales analysis since I need to obtain all the data per item on a monthly basis?

[jpalc-02]

Hi, try that code

But maybe the amount of sales is not squaring, nor are the quantities, some of which do balance but others do not, therefore it affects the amount, some other method will be known.

[SonTran]

Hi,

Try this

SELECT DISTINCT T90."ItemCode", T90."Dscription"
, SUM(T90."CANTIDAD") as "CANTIDAD" ,SUM(T90."Importe de ventas soles") AS "IMPORTE VENTA SOLES"

FROM

(

SELECT T1."ItemCode", T1."Dscription" , SUM(T1."Quantity") AS "CANTIDAD",T1."DocDate",SUM(T0."DocTotal") AS "Importe de ventas soles"

FROM OINV T0

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

WHERE T0."CANCELED" ='N' and T1."Quantity" >0 and t1."DocDate">= '20230801' and t1."DocDate"<= '20230831' AND T0."DocType" = 'I'

--AND WHERE

GROUP BY T1."ItemCode", T1."Dscription",T1."DocDate"

UNION ALL

SELECT T1."ItemCode", T1."Dscription", (-1)* SUM(T1."Quantity") AS "CANTIDAD",T1."DocDate",(-1)*SUM(T0."DocTotal") AS "Importe de ventas soles"

FROM ORIN T0

INNER JOIN RIN1 T1 ON T0."DocEntry" = T1."DocEntry"

WHERE T0."CANCELED" ='N' and T1."Quantity" >0 and t0."DocDate">= '20230801' and t0."DocDate"<= '20230831' AND T0."DocType" = 'I'

GROUP BY T1."ItemCode", T1."Dscription",T1."DocDate",T0."DocTotal"

-- ORDER BY SUM(T1."Quantity")

) T90

WHERE T90."ItemCode" IS NOT NULL and T90."DocDate">='20230801' and T90."DocDate"<='20230831'

GROUP BY T90."ItemCode", T90."Dscription"

ORDER BY SUM(T90."CANTIDAD") DESC

Hope this helps,

Son Tran