informe por grupo de articulos

[Former Member]

hola, tengo la siguiente consulta:

SELECT Distinct T0.[DocNum],

T0.[DocDate],

T9.[ItmsGrpCod],

T9.[ItmsGrpNam],

T7.[VatPrcnt],

T7.[Quantity],

T7.[PriceBefDi],

T7.[DiscPrcnt],

T7.[Price],

(T7.[VatSum]/T7.[Quantity]) 'impuesto linea',

T7.[PriceAfVAT] ,

T7.[LineTotal],

T7.[VatSum],

T7.[GTotal]

FROM OINV T0

INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode

INNER JOIN NNM1 T2 ON T0.Series = T2.Series

INNER JOIN ORCT T3 ON T0.ReceiptNum = T3.DocEntry

INNER JOIN OJDT T4 ON T3.TransId = T4.TransId

INNER JOIN JDT1 T5 ON T4.TransId = T5.TransId

INNER JOIN OACT T6 ON T5.Account = T6.AcctCode

INNER JOIN INV1 T7 ON T0.DocEntry = T7.DocEntry

INNER JOIN OITM T8 ON T7.ItemCode = T8.ItemCode

INNER JOIN OITB T9 ON T8.ItmsGrpCod = T9.ItmsGrpCod

WHERE (T0.[ DocDate] >=[%0] AND T0.[DocDate] <=[%1])

GROUP BY T9.[ItmsGrpCod],T9.[ItmsGrpNam],T0.[DocNum],T0.[DocDate],T7.[VatPrcnt],T7.[Quantity], T7.[PriceBefDi],T7.[DiscPrcnt], T7.[Price], T7.[VatSum], T7.[LineTotal],T7.[GTotal], T7.[PriceAfVAT]

como hago para que me sume el precio unitario (T7.[PriceBefDi]) donde el codigo de grupo del articulo sea igual (T9.[ItmsGrpCod])

por ejemplo: asi me sale esta consulta

COD DESCRIPCION IVA CANTIDAD PRECIO UNITARIO

138 PORCELANA SANITARIOS 16 1 101,331.32

139 PVC CONDUIT 16 50 513

139 PVC CONDUIT 16 200 1,328.00

140 PINTURA AEROSOLES 16 1 13,011.00

140 PINTURA AEROSOLES 16 3 9,418.79

y necesito asi:

COD DESCRIPCION IVA CANTIDAD PRECIO UNITARIO

138 PORCELANA SANITARIOS 16 1 101,331.32

139 PVC CONDUIT 16 200 1,841.00

140 PINTURA AEROSOLES 16 3 22,429.79

saludos cordiales

[Former Member]

resolvi de otra forma gracias

[Former Member]

agregue tambien rango de fecha a la consulta interna pero no me siga dando la suma

SELECT Distinct

T9.ItmsGrpCod,

T9.ItmsGrpNam,

T7.VatPrcnt,

(select Distinct SUM(X.PriceBefDi) FROM INV1 X INNER JOIN OITM Y ON X.ItemCode = Y.ItemCode INNER JOIN OITB Z ON Y.ItmsGrpCod = Z.ItmsGrpCod WHERE Z.ItmsGrpCod = T9.ItmsGrpCod AND X.DocDate >= [%0] AND X.DocDate <= [%1])

FROM OINV T0

INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode

INNER JOIN NNM1 T2 ON T0.Series = T2.Series

INNER JOIN ORCT T3 ON T0.ReceiptNum = T3.DocEntry

INNER JOIN OJDT T4 ON T3.TransId = T4.TransId

INNER JOIN JDT1 T5 ON T4.TransId = T5.TransId

INNER JOIN OACT T6 ON T5.Account = T6.AcctCode

INNER JOIN INV1 T7 ON T0.DocEntry = T7.DocEntry

INNER JOIN OITM T8 ON T7.ItemCode = T8.ItemCode

INNER JOIN OITB T9 ON T8.ItmsGrpCod = T9.ItmsGrpCod

WHERE (T0.DocDate >= [%0] AND T0.DocDate <= [%1])

GROUP BY T9.ItmsGrpCod,T9.ItmsGrpNam,T0.DocNum,T0.DocDate,T7.VatPrcnt,T7.Quantity, T7.PriceBefDi,T7.DiscPrcnt, T7.Price, T7.VatSum, T7.LineTotal,T7.GTotal, T7.PriceAfVAT

[Former Member]

No te agrupa por itmsgrpcode por que, tambien esta agrupando por otras cosas como docnum, docdate,vatsum y un largo etc.

Estas jalando el vatsum de inv1 por ejemplo y este en cada documento puede tener valores distintos en cada fila, osea agrupa por cada vat sum distinto.

Para que te funcione debes selecionar menos campos, o sumando dichos campos, por ejemplo:

Line: -

---

SELECT Distinct --T0.DocNum,

T0.DocDate,

T9.ItmsGrpCod,

T9.ItmsGrpNam,

T7.VatPrcnt,

T7.Quantity,

sum(T7.PriceBefDi),

sum(T7.DiscPrcnt),

sum(T7.Price),

sum(T7.VatSum/T7.Quantity) 'impuesto linea',

sum(T7.PriceAfVAT) ,

sum(T7.LineTotal),

sum(T7.VatSum),

sum(T7.GTotal)

FROM OINV T0

INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode

INNER JOIN NNM1 T2 ON T0.Series = T2.Series

INNER JOIN ORCT T3 ON T0.ReceiptNum = T3.DocEntry

INNER JOIN OJDT T4 ON T3.TransId = T4.TransId

INNER JOIN JDT1 T5 ON T4.TransId = T5.TransId

INNER JOIN OACT T6 ON T5.Account = T6.AcctCode

INNER JOIN INV1 T7 ON T0.DocEntry = T7.DocEntry

INNER JOIN OITM T8 ON T7.ItemCode = T8.ItemCode

INNER JOIN OITB T9 ON T8.ItmsGrpCod = T9.ItmsGrpCod

WHERE (T0. DocDate >='20100401' AND T0.DocDate <='20100430')

GROUP BY T9.ItmsGrpCod,T9.ItmsGrpNam,T0.DocDate,T7.VatPrcnt,T7.Quantity,T7.DiscPrcnt--,t7.price, T7.VatSum, T7.LineTotal,T7.GTotal, T7.PriceAfVAT

line: -

---

sume algunos campos lo que de hecho seran distintos en cada fila y no se agruparan.

saludos

[Former Member]

modifique la consulta con la ayuda que me diste pero no me cuadran las sumas

SELECT Distinct T0.[DocNum],

T0.[DocDate],

T9.[ItmsGrpCod],

T9.[ItmsGrpNam],

T7.[VatPrcnt],

T7.[Quantity],

T7.[PriceBefDi],

(select SUM(A.[PriceBefDi]) FROM INV1 A INNER JOIN OITM B ON A.ItemCode = B.ItemCode INNER JOIN OITB C ON B.ItmsGrpCod = C.ItmsGrpCod WHERE C.ItmsGrpCod = T9.ItmsGrpCod),

T7.[DiscPrcnt],

T7.[Price],

(T7.[VatSum]/T7.[Quantity]) 'impuesto linea',

T7.[PriceAfVAT] ,

T7.[LineTotal],

T7.[VatSum],

T7.[GTotal]

FROM OINV T0

INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode

INNER JOIN NNM1 T2 ON T0.Series = T2.Series

INNER JOIN ORCT T3 ON T0.ReceiptNum = T3.DocEntry

INNER JOIN OJDT T4 ON T3.TransId = T4.TransId

INNER JOIN JDT1 T5 ON T4.TransId = T5.TransId

INNER JOIN OACT T6 ON T5.Account = T6.AcctCode

INNER JOIN INV1 T7 ON T0.DocEntry = T7.DocEntry

INNER JOIN OITM T8 ON T7.ItemCode = T8.ItemCode

INNER JOIN OITB T9 ON T8.ItmsGrpCod = T9.ItmsGrpCod

WHERE (T0.[DocDate] >=[%0] AND T0.[DocDate] <=[%1])

GROUP BY T9.ItmsGrpCod,T9.ItmsGrpNam,T0.DocNum,T0.DocDate,T7.VatPrcnt,T7.Quantity, T7.PriceBefDi,T7.DiscPrcnt, T7.Price, T7.VatSum, T7.LineTotal,T7.GTotal, T7.PriceAfVAT

que estare haciendo mal???

[Former Member]

Hola...

Toma como base el siguiente query...

SELECT Distinct  
T9.ItmsGrpCod, 
T9.ItmsGrpNam, 
T7.VatPrcnt, 
(select SUM(X.Quantity) FROM INV1 X INNER JOIN OITM Y ON X.ItemCode = Y.ItemCode INNER JOIN OITB Z ON Y.ItmsGrpCod = Z.ItmsGrpCod WHERE Z.ItmsGrpCod = T9.ItmsGrpCod), 
(select SUM(X.LineTotal) FROM INV1 X INNER JOIN OITM Y ON X.ItemCode = Y.ItemCode INNER JOIN OITB Z ON Y.ItmsGrpCod = Z.ItmsGrpCod WHERE Z.ItmsGrpCod = T9.ItmsGrpCod)
FROM OINV T0 
INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode 
INNER JOIN NNM1 T2 ON T0.Series = T2.Series 
INNER JOIN ORCT T3 ON T0.ReceiptNum = T3.DocEntry 
INNER JOIN OJDT T4 ON T3.TransId = T4.TransId 
INNER JOIN JDT1 T5 ON T4.TransId = T5.TransId 
INNER JOIN OACT T6 ON T5.Account = T6.AcctCode 
INNER JOIN INV1 T7 ON T0.DocEntry = T7.DocEntry 
INNER JOIN OITM T8 ON T7.ItemCode = T8.ItemCode 
INNER JOIN OITB T9 ON T8.ItmsGrpCod = T9.ItmsGrpCod 

WHERE (T0.DocDate >= [%0] AND T0.DocDate <= [%1]) 
GROUP BY T9.ItmsGrpCod,T9.ItmsGrpNam,T0.DocNum,T0.DocDate,T7.VatPrcnt,T7.Quantity, T7.PriceBefDi,T7.DiscPrcnt, T7.Price, T7.VatSum, T7.LineTotal,T7.GTotal, T7.PriceAfVAT

Lo corriges de acuerdo a tus necesidades,

Espero te sea de ayuda

Saludos Cordiales,