cancel
Showing results for 
Search instead for 
Did you mean: 

Consulta Ventas x mes

Former Member
0 Kudos

Tengo la siguiente consulta pero me trae las lineas de productos repetidos y deseo que me traiga solo una linea con sus valores en cada mes

/SELECT FROM [dbo].[INV1] T0/

DECLARE @fecha1 AS datetime

/* WHERE */

SET @fecha1 = '2009-08-29'--/* T0.DocDate */ '[%0]'

/SELECT FROM [dbo].[INV1] T0/

DECLARE @fecha2 AS datetime

/* WHERE */

SET @fecha2= '2010-07-29'--/* T0.DocDate */ '[%1]'

SELECT T0.ItemCode,

CASE WHEN MONTH(T0.DocDate) = 1 THEN isnull(SUM(T0.Quantity),0) END ENE ,

CASE WHEN MONTH(T0.DocDate) = 2 THEN isnull(SUM(T0.Quantity),0) END FEB,

CASE WHEN MONTH(T0.DocDate) = 3 THEN isnull(SUM(T0.Quantity),0) END MAR ,

CASE WHEN MONTH(T0.DocDate) = 4 THEN isnull(SUM(T0.Quantity),0) END ABR,

CASE WHEN MONTH(T0.DocDate) = 5 THEN isnull(SUM(T0.Quantity),0) END MAY ,

CASE WHEN MONTH(T0.DocDate) = 6 THEN isnull(SUM(T0.Quantity),0) END JUN,

CASE WHEN MONTH(T0.DocDate) = 7 THEN isnull(SUM(T0.Quantity),0) END JUL ,

CASE WHEN MONTH(T0.DocDate) = 8 THEN isnull(SUM(T0.Quantity),0) END AGO,

CASE WHEN MONTH(T0.DocDate) = 9 THEN isnull(SUM(T0.Quantity),0) END SEP ,

CASE WHEN MONTH(T0.DocDate) = 10 THEN isnull(SUM(T0.Quantity),0) END OCT,

CASE WHEN MONTH(T0.DocDate) = 11 THEN isnull(SUM(T0.Quantity),0) END NOV ,

CASE WHEN MONTH(T0.DocDate) = 12 THEN isnull(SUM(T0.Quantity),0) END DIC

FROM INV1 T0 INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode

INNER JOIN OINV T2 ON T0.DocEntry = T2.DocEntry

WHERE (MONTH(T0.DocDate) >= MONTH(@fecha1) OR

MONTH(T0.DocDate) <= MONTH(@fecha1)) AND

YEAR(T0.DocDate) = YEAR(@fecha1)

group by T0.ItemCode, T0.DocDate

Accepted Solutions (1)

Accepted Solutions (1)

former_member212657
Active Participant
0 Kudos

Hola que tal, no seria mejor que trataras con la tabla OINV que es la padre de INV1? Desde OINV haces referencia a INV1 para que te de un resultado que te acerque unpoco mas a lo que quieres por que es el unico problema que veo en tu query.

Saludos

Angel

Answers (2)

Answers (2)

Former Member
0 Kudos

Hola

Puedes tomar como una base el siguiente query..

SELECT DISTINCT F.ItemCode, 
(SELECT SUM(T0.Quantity) FROM INV1 T0 INNER JOIN OINV T1 ON T0.DocEntry = T1.DocEntry WHERE MONTH(T0.DocDate) = '01' AND T0.ItemCode = F.ItemCode) AS 'ENE',
(SELECT SUM(T0.Quantity) FROM INV1 T0 INNER JOIN OINV T1 ON T0.DocEntry = T1.DocEntry WHERE MONTH(T0.DocDate) = '02' AND T0.ItemCode = F.ItemCode) AS 'FEB',
(SELECT SUM(T0.Quantity) FROM INV1 T0 INNER JOIN OINV T1 ON T0.DocEntry = T1.DocEntry WHERE MONTH(T0.DocDate) = '03' AND T0.ItemCode = F.ItemCode) AS 'MAR',
(SELECT SUM(T0.Quantity) FROM INV1 T0 INNER JOIN OINV T1 ON T0.DocEntry = T1.DocEntry WHERE MONTH(T0.DocDate) = '04' AND T0.ItemCode = F.ItemCode) AS 'ABR',
(SELECT SUM(T0.Quantity) FROM INV1 T0 INNER JOIN OINV T1 ON T0.DocEntry = T1.DocEntry WHERE MONTH(T0.DocDate) = '05' AND T0.ItemCode = F.ItemCode) AS 'MAY',
(SELECT SUM(T0.Quantity) FROM INV1 T0 INNER JOIN OINV T1 ON T0.DocEntry = T1.DocEntry WHERE MONTH(T0.DocDate) = '06' AND T0.ItemCode = F.ItemCode) AS 'JUN',
(SELECT SUM(T0.Quantity) FROM INV1 T0 INNER JOIN OINV T1 ON T0.DocEntry = T1.DocEntry WHERE MONTH(T0.DocDate) = '07' AND T0.ItemCode = F.ItemCode) AS 'JUL',
(SELECT SUM(T0.Quantity) FROM INV1 T0 INNER JOIN OINV T1 ON T0.DocEntry = T1.DocEntry WHERE MONTH(T0.DocDate) = '08' AND T0.ItemCode = F.ItemCode) AS 'AGO',
(SELECT SUM(T0.Quantity) FROM INV1 T0 INNER JOIN OINV T1 ON T0.DocEntry = T1.DocEntry WHERE MONTH(T0.DocDate) = '09' AND T0.ItemCode = F.ItemCode) AS 'SEP',
(SELECT SUM(T0.Quantity) FROM INV1 T0 INNER JOIN OINV T1 ON T0.DocEntry = T1.DocEntry WHERE MONTH(T0.DocDate) = '10' AND T0.ItemCode = F.ItemCode) AS 'OCT',
(SELECT SUM(T0.Quantity) FROM INV1 T0 INNER JOIN OINV T1 ON T0.DocEntry = T1.DocEntry WHERE MONTH(T0.DocDate) = '11' AND T0.ItemCode = F.ItemCode) AS 'NOV',
(SELECT SUM(T0.Quantity) FROM INV1 T0 INNER JOIN OINV T1 ON T0.DocEntry = T1.DocEntry WHERE MONTH(T0.DocDate) = '12' AND T0.ItemCode = F.ItemCode) AS 'DIC'
FROM INV1 F

Luego, lo adaptas a los requerimientos adicionales, me parece que debieras considerar también que habrán Facturas que pueden tener notas de crédito.

Espero te sea de ayuda

Saludos Cordiales,

0 Kudos

Hola ... en cierta ocasion me toco hacer algo parecido .... te dejo lo que hice.....

--CONSULTA CON TABLAS DE SAP SIN VARIABLES

SELECT , , , AS Enero, AS Febrero, AS Marzo, AS Abril,

AS Mayo, AS Junio, AS Julio, AS Agosto, AS Septiembre, AS Octubre,

AS Noviembre, AS Diciembre

FROM (

SELECT T1.[ItemCode] AS 'Código', T1.[ItemName] AS 'Nombre del Artículo', T0.[Warehouse] AS 'Almacén', MONTH(T0.[DocDate]) ,

ISNULL(SUM(T0.[OutQty])-SUM(T0.[InQty]),0) AS 'Total'

FROM OINM T0 INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode

WHERE T0.[ItemCode] = T1.[ItemCode]

AND T0.[DocDate] Between '05/01/2007' AND '12/31/2007' -- NO CON VARIABLE SEGUN SAP

AND T0.[TransType] IN ('13', '14', '15', '16', '60') AND T1.[PrchseItem] = 'Y'

GROUP BY T1.[ItemCode], T1.[ItemName], T0.[Warehouse], T0.[DocDate]

) pvt

PIVOT (SUM(Total) FOR IN (,[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) AS Child

ORDER BY

--CONSULTA CON TABLAS DE SAP CON VARIABLES

SELECT , , , AS Enero, AS Febrero, AS Marzo, AS Abril,

AS Mayo, AS Junio, AS Julio, AS Agosto, AS Septiembre, AS Octubre,

AS Noviembre, AS Diciembre

FROM (

SELECT T1.[ItemCode] AS 'Código', T1.[ItemName] AS 'Nombre del Artículo', T0.[Warehouse] AS 'Almacén', MONTH(T0.[DocDate]) ,

ISNULL(SUM(T0.[OutQty])-SUM(T0.[InQty]),0) AS 'Total'

FROM OINM T0 INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode

WHERE T0.[ItemCode] = T1.[ItemCode]

AND T0.[DocDate] Between '[%0]' AND '[%1]' -- NO CON VARIABLE SEGUN SAP

AND T0.[TransType] IN ('13', '14', '15', '16', '60') AND T1.[PrchseItem] = 'Y'

GROUP BY T1.[ItemCode], T1.[ItemName], T0.[Warehouse], T0.[DocDate]

) pvt

PIVOT (SUM(Total) FOR IN (,[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) AS Child

ORDER BY

Con el detalle que no logre correr la consulta desde SAP, solo en el SQL.

Espero te sirva de ayuda aunque sea ..... jejejejejeje!!!!

Saludos