Sumar Consumo Componentes en OF.

[Former Member]

Estimados:

Tengo una consulta en la que obtengo el total del consumo de los componentes (columna Valor Consumo) de una orden de fabricacion, de acuerdo a las entregas de materiales y a las terminaciones report.

Pero ahora necesito sacar el total o la suma de estos totales y dividirlo por la cantidad "completada" y asi obtener el costo del producto.

Alguien me puede ayudar a resolver mi problema?

Adjunto query

SELECT DISTINCT T0.[ItemCode]'Prod.a Fab',T4.ItemName'Nombre',T0.[CmpltQty]'Cant.Fab',T0.[DocNum]'OF',T1.[ItemCode]'MP, MO, MAQ',T3.[ItemName]'Descripcion MP,MO,MAQ', T1.[BaseQty]'Cant.Base',SUM(T2.[Quantity])'Consumo',SUM(T2.[Quantity]*T2.[Price])'Valor Consumo',T3.[ItmsGrpCod] FROM OWOR T0 INNER JOIN WOR1 T1 ON T0.DocEntry = T1.DocEntry

INNER JOIN IGE1 T2 ON T0.[DocNum] =T2.[BaseRef] AND T1.[ItemCode] = T2.[ItemCode]

INNER JOIN OITM T3 ON T1.ItemCode = T3.ItemCode

INNER JOIN OITM T4 ON T0.ItemCode = T4.ItemCode

WHERE T0.[ItemCode] =[%0] and T3.[ItmsGrpCod] = 103 and T0.[CmpltQty] >=T0.[PlannedQty]

Group by T0.[ItemCode],T4.ItemName,T0.[CmpltQty],T0.[DocNum],T1.[ItemCode], T3.[ItemName],T1.[BaseQty],T3.[ItmsGrpCod]

UNION ALL

SELECT DISTINCT T0.[ItemCode],T4.ItemName,T0.[CmpltQty],T0.[DocNum]'OF',T1.[ItemCode]'MP, MO, MAQ',T3.[ItemName]'Descripcion',T1.[BaseQty], SUM(T2.[Quantity]T1.[BaseQty]),( SUM(T2.[Quantity]T1.[BaseQty])* T3.[AvgPrice]),T3.[ItmsGrpCod] FROM OWOR T0 INNER JOIN WOR1 T1 ON T0.DocEntry = T1.DocEntry

INNER JOIN IGN1 T2 ON T0.[DocNum] =T2.[BaseRef]

INNER JOIN OITM T3 ON T1.ItemCode = T3.ItemCode

INNER JOIN OITM T4 ON T0.ItemCode = T4.ItemCode

WHERE T0.[ItemCode] =[%0] and T3.[ItmsGrpCod] = 110 and T0.[CmpltQty] >=T0.[PlannedQty]

Group by T0.[ItemCode],T4.ItemName,T0.[CmpltQty],T0.[DocNum],T1.[ItemCode], T3.[ItemName],T1.[BaseQty],T3.[AvgPrice],T3.[ItmsGrpCod]

union all

SELECT DISTINCT T0.[ItemCode],T4.ItemName,T0.[CmpltQty], T0.[DocNum]'OF',T1.[ItemCode]'MP, MO, MAQ',T3.[ItemName]'Descripcion', T1.[BaseQty], SUM(T2.[Quantity]T1.[BaseQty]),( SUM(T2.[Quantity]T1.[BaseQty])* T3.[AvgPrice]),T3.[ItmsGrpCod] FROM OWOR T0 INNER JOIN WOR1 T1 ON T0.DocEntry = T1.DocEntry

INNER JOIN IGN1 T2 ON T0.[DocNum] =T2.[BaseRef]

INNER JOIN OITM T3 ON T1.ItemCode = T3.ItemCode

INNER JOIN OITM T4 ON T0.ItemCode = T4.ItemCode

WHERE T0.[ItemCode] =[%0] and T3.[ItmsGrpCod] = 108 and T0.[CmpltQty] >=T0.[PlannedQty]

Group by T0.[ItemCode],T4.ItemName,T0.[CmpltQty],T0.[DocNum],T1.[ItemCode], T3.[ItemName],T1.[BaseQty],T3.[AvgPrice],T3.[ItmsGrpCod]

ORDER BY T0.[DocNum],T3.[ItmsGrpCod]

Muchas gracias.

VMC.

[felipe_loyolarodriguez]

Hola vivi, necesitas hacer la suma de toda esa query para lo que estas pidiendo?

Si es asi trata con esto o tomalo como ejemplo

SELECT DISTINCT T0.ItemCode'Prod.a Fab',T4.ItemName'Nombre',T0.CmpltQty'Cant.Fab',T0.DocNum'OF',T1.ItemCode'MP, MO, MAQ',T3.ItemName'Descripcion MP,MO,MAQ', T1.BaseQty'Cant.Base',SUM(T2.Quantity)'Consumo',SUM(T2.Quantity*T2.Price)'Valor Consumo',T3.ItmsGrpCod 
FROM OWOR T0  
INNER JOIN WOR1 T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN  IGE1 T2 ON T0.DocNum =T2.BaseRef AND T1.ItemCode =  T2.ItemCode
INNER JOIN OITM T3 ON T1.ItemCode = T3.ItemCode
INNER JOIN OITM T4 ON T0.ItemCode = T4.ItemCode
WHERE T0.ItemCode =[%0] and T3.ItmsGrpCod  = 103 and T0.CmpltQty >=T0.PlannedQty
Group by T0.ItemCode,T4.ItemName,T0.CmpltQty,T0.DocNum,T1.ItemCode, T3.ItemName,T1.BaseQty,T3.ItmsGrpCod
UNION ALL
 
SELECT DISTINCT T0.ItemCode,T4.ItemName,T0.CmpltQty,T0.DocNum'OF',T1.ItemCode'MP, MO, MAQ',T3.ItemName'Descripcion',T1.BaseQty, SUM(T2.Quantity*T1.BaseQty),( SUM(T2.Quantity*T1.BaseQty)* T3.AvgPrice),T3.ItmsGrpCod 
FROM OWOR T0  
INNER JOIN WOR1 T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN  IGN1 T2 ON T0.DocNum =T2.BaseRef
INNER JOIN OITM T3 ON T1.ItemCode = T3.ItemCode
INNER JOIN OITM T4 ON T0.ItemCode = T4.ItemCode
WHERE T0.ItemCode =[%0] and T3.ItmsGrpCod  = 110 and T0.CmpltQty >=T0.PlannedQty
Group by T0.ItemCode,T4.ItemName,T0.CmpltQty,T0.DocNum,T1.ItemCode, T3.ItemName,T1.BaseQty,T3.AvgPrice,T3.ItmsGrpCod
UNION ALL
 
SELECT DISTINCT T0.ItemCode,T4.ItemName,T0.CmpltQty, T0.DocNum'OF',T1.ItemCode'MP, MO, MAQ',T3.ItemName'Descripcion', T1.BaseQty, SUM(T2.Quantity*T1.BaseQty),( SUM(T2.Quantity*T1.BaseQty)* T3.AvgPrice),T3.ItmsGrpCod   
FROM OWOR T0  
INNER JOIN WOR1 T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN  IGN1 T2 ON T0.DocNum =T2.BaseRef
INNER JOIN OITM T3 ON T1.ItemCode = T3.ItemCode
INNER JOIN OITM T4 ON T0.ItemCode = T4.ItemCode
WHERE T0.ItemCode =[%0] and T3.ItmsGrpCod  = 108 and T0.CmpltQty >=T0.PlannedQty
Group by T0.ItemCode,T4.ItemName,T0.CmpltQty,T0.DocNum,T1.ItemCode, T3.ItemName,T1.BaseQty,T3.AvgPrice,T3.ItmsGrpCod
UNION ALL
 
SELECT DISTINCT /*campos, sumas de campos, etc...ejemplo*/ NULL, NULL, SUM(Cantidad), T0.OF, NULL, NULL, SUM(Cant.Base), SUM(Consumo), Sum(valor_consumo), NULL
FROM (
	SELECT DISTINCT T0.ItemCode AS "Prod", T4.ItemName AS "Nombre", T0.CmpltQty AS "Cantidad", T0.DocNum AS "OF",T1.ItemCode AS "MP_MO_MAQ", T3.ItemName AS "Descripcion_MP,MO,MAQ", T1.BaseQty AS "Cant.Base", SUM(T2.Quantity) AS "Consumo", SUM(T2.Quantity*T2.Price) AS "Valor_Consumo", T3.ItmsGrpCod 
	FROM OWOR T0  
	INNER JOIN WOR1 T1 ON T0.DocEntry = T1.DocEntry
	INNER JOIN  IGE1 T2 ON T0.DocNum =T2.BaseRef AND T1.ItemCode =  T2.ItemCode
	INNER JOIN OITM T3 ON T1.ItemCode = T3.ItemCode
	INNER JOIN OITM T4 ON T0.ItemCode = T4.ItemCode
	WHERE T0.ItemCode =[%0] and T3.ItmsGrpCod  = 103 and T0.CmpltQty >=T0.PlannedQty
	Group by T0.ItemCode,T4.ItemName,T0.CmpltQty,T0.DocNum,T1.ItemCode, T3.ItemName,T1.BaseQty,T3.ItmsGrpCod
	UNION ALL
	
	SELECT DISTINCT T0.ItemCode,T4.ItemName,T0.CmpltQty,T0.DocNum'OF',T1.ItemCode'MP, MO, MAQ',T3.ItemName'Descripcion',T1.BaseQty, SUM(T2.Quantity*T1.BaseQty),( SUM(T2.Quantity*T1.BaseQty)* T3.AvgPrice),T3.ItmsGrpCod 
	FROM OWOR T0  
	INNER JOIN WOR1 T1 ON T0.DocEntry = T1.DocEntry
	INNER JOIN  IGN1 T2 ON T0.DocNum =T2.BaseRef
	INNER JOIN OITM T3 ON T1.ItemCode = T3.ItemCode
	INNER JOIN OITM T4 ON T0.ItemCode = T4.ItemCode
	WHERE T0.ItemCode =[%0] and T3.ItmsGrpCod  = 110 and T0.CmpltQty >=T0.PlannedQty
	Group by T0.ItemCode,T4.ItemName,T0.CmpltQty,T0.DocNum,T1.ItemCode, T3.ItemName,T1.BaseQty,T3.AvgPrice,T3.ItmsGrpCod
	UNION ALL
	
	SELECT DISTINCT T0.ItemCode,T4.ItemName,T0.CmpltQty, T0.DocNum'OF',T1.ItemCode'MP, MO, MAQ',T3.ItemName'Descripcion', T1.BaseQty, SUM(T2.Quantity*T1.BaseQty),( SUM(T2.Quantity*T1.BaseQty)* T3.AvgPrice),T3.ItmsGrpCod   
	FROM OWOR T0  
	INNER JOIN WOR1 T1 ON T0.DocEntry = T1.DocEntry
	INNER JOIN  IGN1 T2 ON T0.DocNum =T2.BaseRef
	INNER JOIN OITM T3 ON T1.ItemCode = T3.ItemCode
	INNER JOIN OITM T4 ON T0.ItemCode = T4.ItemCode
	WHERE T0.ItemCode =[%0] and T3.ItmsGrpCod  = 108 and T0.CmpltQty >=T0.PlannedQty
	Group by T0.ItemCode,T4.ItemName,T0.CmpltQty,T0.DocNum,T1.ItemCode, T3.ItemName,T1.BaseQty,T3.AvgPrice,T3.ItmsGrpCod
	ORDER BY T0.DocNum,T3.ItmsGrpCod
	UNION ALL
) AS A0
ORDER BY 4,10,1

Saludos

Edited by: Floyola on Sep 13, 2011 8:24 AM

Edited by: Floyola on Sep 13, 2011 2:22 PM

[Former Member]

Felipe:

Gracias por responder.

Al ejecutar la query que me indicas, me envia el siguiente error:

1). [SQL Server Native Client 10.0][SQL Server]Incorrect syntax near the keyword 'OF'. 2). [SQL Server Native Client 10.0][SQL Server]Incorrect syntax near the keyword 'ORDER'. 3). [SQL Server Native Client 10.0][SQL Serv

Por lo tanto la modifiqué, pero la suma que me da es el total de todas las OF del producto y lo que necesito es la suma por OF.

Espero me puedas ayudar

Adjunto query modificada:

SELECT DISTINCT T0.ItemCode'Prod.a Fab',T4.ItemName'Nombre',T0.CmpltQty'Cant.Fab',T0.DocNum'OF',T1.ItemCode'MP, MO, MAQ',T3.ItemName'Descripcion MP,MO,MAQ', T1.BaseQty'Cant.Base',SUM(T2.Quantity)'Consumo',SUM(T2.Quantity*T2.Price)'Valor Consumo',T3.ItmsGrpCod

FROM OWOR T0

INNER JOIN WOR1 T1 ON T0.DocEntry = T1.DocEntry

INNER JOIN IGE1 T2 ON T0.DocNum =T2.BaseRef AND T1.ItemCode = T2.ItemCode

INNER JOIN OITM T3 ON T1.ItemCode = T3.ItemCode

INNER JOIN OITM T4 ON T0.ItemCode = T4.ItemCode

WHERE T0.ItemCode =[%0] and T3.ItmsGrpCod = 103 and T0.CmpltQty >=T0.PlannedQty

Group by T0.ItemCode,T4.ItemName,T0.CmpltQty,T0.DocNum,T1.ItemCode, T3.ItemName,T1.BaseQty,T3.ItmsGrpCod

UNION ALL

SELECT DISTINCT T0.ItemCode,T4.ItemName,T0.CmpltQty,T0.DocNum'OF',T1.ItemCode'MP, MO, MAQ',T3.ItemName'Descripcion',T1.BaseQty, SUM(T2.QuantityT1.BaseQty),( SUM(T2.QuantityT1.BaseQty)* T3.AvgPrice),T3.ItmsGrpCod

FROM OWOR T0

INNER JOIN WOR1 T1 ON T0.DocEntry = T1.DocEntry

INNER JOIN IGN1 T2 ON T0.DocNum =T2.BaseRef

INNER JOIN OITM T3 ON T1.ItemCode = T3.ItemCode

INNER JOIN OITM T4 ON T0.ItemCode = T4.ItemCode

WHERE T0.ItemCode =[%0] and T3.ItmsGrpCod = 110 and T0.CmpltQty >=T0.PlannedQty

Group by T0.ItemCode,T4.ItemName,T0.CmpltQty,T0.DocNum,T1.ItemCode, T3.ItemName,T1.BaseQty,T3.AvgPrice,T3.ItmsGrpCod

UNION ALL

SELECT DISTINCT T0.ItemCode,T4.ItemName,T0.CmpltQty, T0.DocNum'OF',T1.ItemCode'MP, MO, MAQ',T3.ItemName'Descripcion', T1.BaseQty, SUM(T2.QuantityT1.BaseQty),( SUM(T2.QuantityT1.BaseQty)* T3.AvgPrice),T3.ItmsGrpCod

FROM OWOR T0

INNER JOIN WOR1 T1 ON T0.DocEntry = T1.DocEntry

INNER JOIN IGN1 T2 ON T0.DocNum =T2.BaseRef

INNER JOIN OITM T3 ON T1.ItemCode = T3.ItemCode

INNER JOIN OITM T4 ON T0.ItemCode = T4.ItemCode

WHERE T0.ItemCode =[%0] and T3.ItmsGrpCod = 108 and T0.CmpltQty >=T0.PlannedQty

Group by T0.ItemCode,T4.ItemName,T0.CmpltQty,T0.DocNum,T1.ItemCode, T3.ItemName,T1.BaseQty,T3.AvgPrice,T3.ItmsGrpCod

UNION ALL

SELECT DISTINCT /campos, sumas de campos, etc...ejemplo/ NULL, NULL, SUM(Cantidad),NULL, NULL, NULL, NULL, SUM(Consumo), Sum(valor_consumo), NULL

FROM (

SELECT DISTINCT T0.ItemCode AS "Prod", T4.ItemName AS "Nombre", T0.CmpltQty AS "Cantidad", T0.DocNum AS "OF",T1.ItemCode AS "MP_MO_MAQ", T3.ItemName AS "Descripcion_MP,MO,MAQ", T1.BaseQty AS "Cant.Base", SUM(T2.Quantity) AS "Consumo", SUM(T2.Quantity*T2.Price) AS "Valor_Consumo", T3.ItmsGrpCod

FROM OWOR T0

INNER JOIN WOR1 T1 ON T0.DocEntry = T1.DocEntry

INNER JOIN IGE1 T2 ON T0.DocNum =T2.BaseRef AND T1.ItemCode = T2.ItemCode

INNER JOIN OITM T3 ON T1.ItemCode = T3.ItemCode

INNER JOIN OITM T4 ON T0.ItemCode = T4.ItemCode

WHERE T0.ItemCode =[%0] and T3.ItmsGrpCod = 103 and T0.CmpltQty >=T0.PlannedQty

Group by T0.ItemCode,T4.ItemName,T0.CmpltQty,T0.DocNum,T1.ItemCode, T3.ItemName,T1.BaseQty,T3.ItmsGrpCod

UNION ALL

SELECT DISTINCT T0.ItemCode,T4.ItemName,T0.CmpltQty,T0.DocNum'OF',T1.ItemCode'MP, MO, MAQ',T3.ItemName'Descripcion',T1.BaseQty, SUM(T2.QuantityT1.BaseQty),( SUM(T2.QuantityT1.BaseQty)* T3.AvgPrice),T3.ItmsGrpCod

FROM OWOR T0

INNER JOIN WOR1 T1 ON T0.DocEntry = T1.DocEntry

INNER JOIN IGN1 T2 ON T0.DocNum =T2.BaseRef

INNER JOIN OITM T3 ON T1.ItemCode = T3.ItemCode

INNER JOIN OITM T4 ON T0.ItemCode = T4.ItemCode

WHERE T0.ItemCode =[%0] and T3.ItmsGrpCod = 110 and T0.CmpltQty >=T0.PlannedQty

Group by T0.ItemCode,T4.ItemName,T0.CmpltQty,T0.DocNum,T1.ItemCode, T3.ItemName,T1.BaseQty,T3.AvgPrice,T3.ItmsGrpCod

UNION ALL

SELECT DISTINCT T0.ItemCode,T4.ItemName,T0.CmpltQty, T0.DocNum'OF',T1.ItemCode'MP, MO, MAQ',T3.ItemName'Descripcion', T1.BaseQty, SUM(T2.QuantityT1.BaseQty),( SUM(T2.QuantityT1.BaseQty)* T3.AvgPrice),T3.ItmsGrpCod

FROM OWOR T0

INNER JOIN WOR1 T1 ON T0.DocEntry = T1.DocEntry

INNER JOIN IGN1 T2 ON T0.DocNum =T2.BaseRef

INNER JOIN OITM T3 ON T1.ItemCode = T3.ItemCode

INNER JOIN OITM T4 ON T0.ItemCode = T4.ItemCode

WHERE T0.ItemCode =[%0] and T3.ItmsGrpCod = 108 and T0.CmpltQty >=T0.PlannedQty

Group by T0.ItemCode,T4.ItemName,T0.CmpltQty,T0.DocNum,T1.ItemCode, T3.ItemName,T1.BaseQty,T3.AvgPrice,T3.ItmsGrpCod)

AS A0

ORDER BY T3.ItmsGrpCod, T0.ItemCode

Saludos,

VMC