Hi to all, anyone could help me with this report ?
I have this one:
SELECT 'Factura',T0.ItemCode, T0.Dscription, SUM(Quantity) as Cantidad, Sum(LineTotal) as Total
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
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
But now I want to introduce this sentence in:
Select I.ItemCode,I.ItemName,
(select price from ITM1 where ItemCode=I.ItemCode and PriceList=1),
(select price from ITM1 where ItemCode=I.ItemCode and PriceList=2),
(select price from ITM1 where ItemCode=I.ItemCode and PriceList=3),
(select price from ITM1 where ItemCode=I.ItemCode and PriceList=4)
From OITM I
Could you help me ?
Many thanks.