Skip to Content
0
Former Member
Feb 17, 2009 at 08:56 AM

Anyone could help me with this report. ?

20 Views

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.