on 08-23-2010 5:56 PM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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,
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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,
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
--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,
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
Con el detalle que no logre correr la consulta desde SAP, solo en el SQL.
Espero te sirva de ayuda aunque sea ..... jejejejejeje!!!!
Saludos
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
89 | |
7 | |
6 | |
3 | |
3 | |
3 | |
3 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.