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