Un saludo para todos,
Recurro nuevamente a su ayuda dado que estoy teniendo inconvenientes con un query que me muestra el acumulado mes a mes de ventas por cada empleado de ventas, estoy teniendo diferencias si lo comparo con el análisis de ventas nativo de SAP.
El motivo por el cual recurro al query es que debo añadirle unos campos adicionales que me pidió el área de contabilidad, uno de ellos sería el costo acumulado de las facturas mes a mes.
No he podido encontrar el causante de las diferencias entre los informes, por lo que recurro a su ayuda para poder solucionar este problema.
Adjunto query:
SELECT T0.SlpCode, T0.SlpName,
ISNULL((SELECT SUM(T.DocTotal-T.Vatsum) FROM OINV T WHERE MONTH(T.DocDate) = 1 AND YEAR(T.DocDate)=2020 AND T.SlpCode = T0.SlpCode),0)-ISNULL((SELECT SUM(Y.DocTotal-Y.Vatsum) FROM ORIN Y WHERE MONTH(Y.DocDate) = 1 AND YEAR(Y.DocDate)=2020 AND Y.SlpCode = T0.SlpCode),0)'Enero',
ISNULL((SELECT SUM(T.DocTotal-T.Vatsum) FROM OINV T WHERE MONTH(T.DocDate) = 2 AND YEAR(T.DocDate)=2020 AND T.SlpCode = T0.SlpCode),0)-ISNULL((SELECT SUM(Y.DocTotal-Y.Vatsum) FROM ORIN Y WHERE MONTH(Y.DocDate) = 2 AND YEAR(Y.DocDate)=2020 AND Y.SlpCode = T0.SlpCode),0)'Febrero',
ISNULL((SELECT SUM(T.DocTotal-T.Vatsum) FROM OINV T WHERE MONTH(T.DocDate) = 3 AND YEAR(T.DocDate)=2020 AND T.SlpCode = T0.SlpCode),0)-ISNULL((SELECT SUM(Y.DocTotal-Y.Vatsum) FROM ORIN Y WHERE MONTH(Y.DocDate) = 3 AND YEAR(Y.DocDate)=2020 AND Y.SlpCode = T0.SlpCode),0)'Marzo',
ISNULL((SELECT SUM(T.DocTotal-T.Vatsum) FROM OINV T WHERE MONTH(T.DocDate) = 4 AND YEAR(T.DocDate)=2020 AND T.SlpCode = T0.SlpCode),0)-ISNULL((SELECT SUM(Y.DocTotal-Y.Vatsum) FROM ORIN Y WHERE MONTH(Y.DocDate) = 4 AND YEAR(Y.DocDate)=2020 AND Y.SlpCode = T0.SlpCode),0)'Abril',
ISNULL((SELECT SUM(T.DocTotal-T.Vatsum) FROM OINV T WHERE MONTH(T.DocDate) = 5 AND YEAR(T.DocDate)=2020 AND T.SlpCode = T0.SlpCode),0)-ISNULL((SELECT SUM(Y.DocTotal-Y.Vatsum) FROM ORIN Y WHERE MONTH(Y.DocDate) = 5 AND YEAR(Y.DocDate)=2020 AND Y.SlpCode = T0.SlpCode),0)'Mayo',
ISNULL((SELECT SUM(T.DocTotal-T.Vatsum) FROM OINV T WHERE MONTH(T.DocDate) = 6 AND YEAR(T.DocDate)=2020 AND T.SlpCode = T0.SlpCode),0)-ISNULL((SELECT SUM(Y.DocTotal-Y.Vatsum) FROM ORIN Y WHERE MONTH(Y.DocDate) = 6 AND YEAR(Y.DocDate)=2020 AND Y.SlpCode = T0.SlpCode),0)'Junio',
ISNULL((SELECT SUM(T.DocTotal-T.Vatsum) FROM OINV T WHERE MONTH(T.DocDate) = 7 AND YEAR(T.DocDate)=2020 AND T.SlpCode = T0.SlpCode),0)-ISNULL((SELECT SUM(Y.DocTotal-Y.Vatsum) FROM ORIN Y WHERE MONTH(Y.DocDate) = 7 AND YEAR(Y.DocDate)=2020 AND Y.SlpCode = T0.SlpCode),0)'Julio',
ISNULL((SELECT SUM(T.DocTotal-T.Vatsum) FROM OINV T WHERE MONTH(T.DocDate) = 8 AND YEAR(T.DocDate)=2020 AND T.SlpCode = T0.SlpCode),0)-ISNULL((SELECT SUM(Y.DocTotal-Y.Vatsum) FROM ORIN Y WHERE MONTH(Y.DocDate) = 8 AND YEAR(Y.DocDate)=2020 AND Y.SlpCode = T0.SlpCode),0)'Agosto',
ISNULL((SELECT SUM(T.DocTotal-T.Vatsum) FROM OINV T WHERE MONTH(T.DocDate) = 9 AND YEAR(T.DocDate)=2020 AND T.SlpCode = T0.SlpCode),0)-ISNULL((SELECT SUM(Y.DocTotal-Y.Vatsum) FROM ORIN Y WHERE MONTH(Y.DocDate) = 9 AND YEAR(Y.DocDate)=2020 AND Y.SlpCode = T0.SlpCode),0)'Septiembre',
ISNULL((SELECT SUM(T.DocTotal-T.Vatsum) FROM OINV T WHERE MONTH(T.DocDate) = 10 AND YEAR(T.DocDate)=2020 AND T.SlpCode = T0.SlpCode),0)-ISNULL((SELECT SUM(Y.DocTotal-Y.Vatsum) FROM ORIN Y WHERE MONTH(Y.DocDate) = 10 AND YEAR(Y.DocDate)=2020 AND Y.SlpCode = T0.SlpCode),0)'Octubre',
ISNULL((SELECT SUM(T.DocTotal-T.Vatsum) FROM OINV T WHERE MONTH(T.DocDate) = 11 AND YEAR(T.DocDate)=2020 AND T.SlpCode = T0.SlpCode),0)-ISNULL((SELECT SUM(Y.DocTotal-Y.Vatsum) FROM ORIN Y WHERE MONTH(Y.DocDate) = 11 AND YEAR(Y.DocDate)=2020 AND Y.SlpCode = T0.SlpCode),0)'Noviembre',
ISNULL((SELECT SUM(T.DocTotal-T.Vatsum) FROM OINV T WHERE MONTH(T.DocDate) = 12 AND YEAR(T.DocDate)=2020 AND T.SlpCode = T0.SlpCode),0)-ISNULL((SELECT SUM(Y.DocTotal-Y.Vatsum) FROM ORIN Y WHERE MONTH(Y.DocDate) = 12 AND YEAR(Y.DocDate)=2020 AND Y.SlpCode = T0.SlpCode),0)'Diciembre',
ISNULL((SELECT SUM(T.DocTotal-T.Vatsum) FROM OINV T WHERE YEAR(T.DocDate)=2020 AND T.SlpCode = T0.SlpCode),0)-ISNULL((SELECT SUM(Y.DocTotal-Y.Vatsum) FROM ORIN Y WHERE YEAR(Y.DocDate)=2020 AND Y.SlpCode = T0.SlpCode),0)'Acumulado 2020'
FROM OSLP T0
ORDER BY T0.SlpCode
Un saludo y muchas gracias.