Buenas tardes,
Requiero de un apoyo para realizar informe de ventas comparativo por mes. Hasta el momento usamos el informe nativo de SAP, pero surgió un requerimiento de agregar campos en el informe, cosa que no se puede hacer en el informe nativo.
Después de navegar por el foro y probar diversas soluciones encontré un query que me da los montos exactos al informe nativo de SAP y pude agregar los campos requeridos al informe.
El apoyo que necesito es para segmentar los montos de Cantidad y total de ventas por mes como lo hace SAP.
De antemano agradezco cualquier apoyo que puedan brindarme.
Saludos.
SELECT T4.CardCode, T4.CardName, T4.address, T5.GroupName, T1.ItemCode, T2.ItemName AS Producto, SUM(T1.Quantity) AS Cantidad,SUM(T1.LineTotal) AS Tventa, T0.DocCur
FROM OINV AS T0
INNER JOIN INV1 AS T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN OITM AS T2 ON T1.ItemCode = T2.ItemCode
INNER JOIN OITB AS T3 ON T2.ItmsGrpCod = T3.ItmsGrpCod
INNER JOIN OCRD AS T4 ON T0.CardCode = T4.CardCode
INNER JOIN OCRG AS T5 ON T4.GroupCode = T5.GroupCode
WHERE T0.DocDate >= CONVERT(datetime,'01/01/2024',103) AND T0.DocDate <= CONVERT(datetime,'13/05/2024',103) AND T0.DocCur = '$'
GROUP BY T4.CardCode, T1.ItemCode, T2.ItemName, T0.DocCur, T4.CardName, T4.address, T5.GroupName
UNION ALL
SELECT T4.CardCode, T4.CardName, T4.address, T5.GroupName, T1.ItemCode, T2.ItemName AS Producto, SUM(T1.Quantity * -1) AS Cantidad, SUM(T1.LineTotal * -1) AS Tventa, T0.DocCur
FROM ORIN T0
INNER JOIN RIN1 T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode
INNER JOIN OITB AS T3 ON T2.ItmsGrpCod = T3.ItmsGrpCod
INNER JOIN OCRD AS T4 ON T0.CardCode = T4.CardCode
INNER JOIN OCRG AS T5 ON T4.GroupCode = T5.GroupCode
WHERE T0.DocDate >= CONVERT(datetime,'01/01/2024',103) AND T0.DocDate <= CONVERT(datetime,'13/05/2024',103) AND T0.DocCur = '$'
GROUP BY T4.CardCode, T1.ItemCode, T2.ItemName, T0.DocCur, T4.CardName, T4.address, T5.GroupName
