Skip to Content
0
Former Member
Feb 19, 2009 at 09:30 AM

Help with this report

10 Views

Hi to all, sorry by this post, but I don´t know how to multiply the colums 'Cantidad' * Price List 1 by example, and put the result in other column.

Anyone could help me please ?

Many thanks.

SELECT 'Factura',T0.ItemCode, T0.Dscription, SUM(Quantity) as Cantidad, Sum(LineTotal) as Total,

(select price from ITM1 where ItemCode=T0.ItemCode and PriceList=1) 'Price List 1',

(select price from ITM1 where ItemCode=T0.ItemCode and PriceList=2) 'Price List 2',

(select price from ITM1 where ItemCode=T0.ItemCode and PriceList=3) 'Price List 3',

(select price from ITM1 where ItemCode=T0.ItemCode and PriceList=4) 'Price List 4',

(select price from ITM1 where ItemCode=T0.ItemCode and PriceList=6) 'Price List 6'

FROM INV1 T0 INNER JOIN OINV T1 ON T0.DocEntry = T1.DocEntry

INNER JOIN OCRD T2 ON T1.CardCode = T2.CardCode

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

WHERE ((T1.CardCode >= '[%1]' AND T1.CardCode <= '[%2]'))

AND T1.DocDueDate >= '[%4]' AND T1.DocDueDate <= '[%5]'

AND T3.SlpName >= '[%6]' AND T3.SlpName <= '[%7]'

GROUP BY T0.ItemCode, T0.Dscription

UNION

SELECT 'Abono',T0.ItemCode, T0.Dscription, SUM(Quantity * -1 ) as Cantidad, Sum(LineTotal * - 1) as Total,

(select price from ITM1 where ItemCode=T0.ItemCode and PriceList=1) 'Price List 1',

(select price from ITM1 where ItemCode=T0.ItemCode and PriceList=2) 'Price List 2',

(select price from ITM1 where ItemCode=T0.ItemCode and PriceList=3) 'Price List 3',

(select price from ITM1 where ItemCode=T0.ItemCode and PriceList=4) 'Price List 4',

(select price from ITM1 where ItemCode=T0.ItemCode and PriceList=6) 'Price List 6'

FROM RIN1 T0 INNER JOIN ORiN T1 ON T0.DocEntry = T1.DocEntry

INNER JOIN OCRD T2 ON T1.CardCode = T2.CardCode

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

WHERE ((T1.CardCode >= '[%1]' AND T1.CardCode <= '[%2]') )

AND T1.DocDueDate >= '[%4]' AND T1.DocDueDate <= '[%5]'

AND T3.SlpName >= '[%6]' AND T3.SlpName <= '[%7]'

GROUP BY T0.ItemCode, T0.Dscription