Sql Query

[antonio_pucholcliment]

Hi,

I make this query:

SELECT 'Facturas', T0.[CardCode], T0.[CardName], T1.[SlpName], SUM(Quantity) as Unidades, SUM(LineTotal) as Euros

FROM OCRD T0 INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode INNER JOIN OINV T2 ON T0.CardCode = T2.CardCode INNER JOIN INV1 T3 ON T2.DocEntry = T3.DocEntry WHERE T1.[SlpName] >=[%0] AND T1.[SlpName] <=[%1] AND T2.[DocDueDate] >=[%2] AND T2.[DocDueDate] <=[%3]

GROUP BY T0.[CardCode], T0.[CardName], T1.[SlpName]

UNION

SELECT 'Abonos', T0.[CardCode], T0.[CardName], T1.[SlpName], SUM(Quantity *-1) as Unidades, SUM(LineTotal *-1) as Euros

FROM OCRD T0 INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode INNER JOIN ORIN T2 ON T0.CardCode = T2.CardCode INNER JOIN RIN1 T3 ON T2.DocEntry = T3.DocEntry WHERE T1.[SlpName] >=[%0] AND T1.[SlpName] <=[%1] AND T2.[DocDueDate] >=[%2] AND T2.[DocDueDate] <=[%3]

GROUP BY T0.[CardCode], T0.[CardName], T1.[SlpName]

But now I want to modify this one to obtain items sold by customer grouped choosing the date that I want, ex: from 01/01/2010 to 31/12/2010

Thanks.

Antonio.

[antonio_pucholcliment]

Dear Balakumar,

The query dont works, sql tell me that there is an error in column groupname.

Balaji, your query do the same as mine.

Thanks.

[kvbalakumar]

Hi Antonio,

Try this

SELECT 'Facturas', T0.CardCode, T0.CardName, T1.SlpName, T4.[GroupName], SUM(Quantity) as Unidades, SUM(LineTotal) as Euros
FROM OCRD T0 INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode INNER JOIN OINV T2 ON T0.CardCode = T2.CardCode INNER JOIN INV1 T3 ON T2.DocEntry = T3.DocEntry inner join OCRG T4 ON T0.GroupCode = T4.GroupCode WHERE T1.SlpName >=%0 AND T1.SlpName <=%1 AND T2.DocDueDate >=%2 AND T2.DocDueDate <=%3
GROUP BY T0.CardCode, T0.CardName, T1.SlpName, T4.[GroupName]
UNION
SELECT 'Abonos', T0.CardCode, T0.CardName, T1.SlpName, T4.[GroupName], SUM(Quantity *-1) as Unidades, SUM(LineTotal *-1) as Euros
FROM OCRD T0 INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode INNER JOIN ORIN T2 ON T0.CardCode = T2.CardCode INNER JOIN RIN1 T3 ON T2.DocEntry = T3.DocEntry  inner join OCRG T4 ON T0.GroupCode = T4.GroupCode WHERE T1.SlpName >=%0 AND T1.SlpName <=%1 AND T2.DocDueDate >=%2 AND T2.DocDueDate <=%3
GROUP BY T0.CardCode, T0.CardName, T1.SlpName, T4.[GroupName]

Regards,

Bala

[former_member312729]

Hi Antonio

Try this

SELECT T0.[CardCode], T4.[SlpName], T0.[CardName], T1.[Dscription],sum(T1.[Quantity])[Quantity], T1.[LineTotal] FROM OINV T0  INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OCRD T2 ON T0.CardCode = T2.CardCode INNER JOIN OCRG T3 ON T2.GroupCode = T3.GroupCode INNER JOIN OSLP T4 ON T0.SlpCode = T4.SlpCode WHERE T0.[DocDate] =[%1] AND   T0.[DocDueDate] =[%2] AND  T3.[GroupName] =[%0]  AND T3.[GroupType] ='c'   and T1.[TargetType]<>'14' Group by  T0.[CardCode], T4.[SlpName], T0.[CardName], T1.[Dscription], T1.[LineTotal]

Regards:

Balaji.s

[antonio_pucholcliment]

Dear Balakumar,

This works right, but I dont need that info, I need:

sales grouped by customer, grouped by item name and able to choose period of date.

Thanks.

[kvbalakumar]

Hi ,

Try this

SELECT 'Facturas', T4.[GroupName], T3.ItemCode, SUM(T3.Quantity) as Unidades, SUM(T3.LineTotal) as Euros
FROM OCRD T0 INNER JOIN OINV T2 ON T0.CardCode = T2.CardCode INNER JOIN INV1 T3 ON T2.DocEntry = T3.DocEntry inner join OCRG T4 ON T0.GroupCode = T4.GroupCode WHERE T2.DocDueDate >=[%0] AND T2.DocDueDate <=[%1]
GROUP BY T4.[GroupName],  T3.ItemCode
UNION
SELECT 'Abonos', T4.[GroupName], T3.ItemCode, SUM(T3.Quantity *-1) as Unidades, SUM(T3.LineTotal *-1) as Euros
FROM OCRD T0 INNER JOIN ORIN T2 ON T0.CardCode = T2.CardCode INNER JOIN RIN1 T3 ON T2.DocEntry = T3.DocEntry  inner join OCRG T4 ON T0.GroupCode = T4.GroupCode WHERE T2.DocDueDate >=[%0] AND T2.DocDueDate <=[%1]
GROUP BY T4.[GroupName],  T3.ItemCode

Regards,

Bala

[antonio_pucholcliment]

Dear Balakumar,

Groupname not column valid.

Thanks.

[kvbalakumar]

Try this

SELECT 'Facturas' [Type], T4.GroupName, T3.ItemCode, SUM(T3.Quantity) as Unidades, SUM(T3.LineTotal) as Euros
FROM OCRD T0 INNER JOIN OINV T2 ON T0.CardCode = T2.CardCode INNER JOIN INV1 T3 ON T2.DocEntry = T3.DocEntry inner join OCRG T4 ON T0.GroupCode = T4.GroupCode WHERE T2.DocDueDate >=[%0] AND T2.DocDueDate <=[%1]
GROUP BY T4.GroupName, T3.ItemCode
UNION ALL
SELECT 'Abonos' [Type], T4.GroupName, T3.ItemCode, SUM(T3.Quantity *-1) as Unidades, SUM(T3.LineTotal *-1) as Euros
FROM OCRD T0 INNER JOIN ORIN T2 ON T0.CardCode = T2.CardCode INNER JOIN RIN1 T3 ON T2.DocEntry = T3.DocEntry  inner join OCRG T4 ON T0.GroupCode = T4.GroupCode WHERE T2.DocDueDate >=[%0] AND T2.DocDueDate <=[%1]
GROUP BY T4.GroupName, T3.ItemCode

Regards,

Bala

[antonio_pucholcliment]

Yesss !!!

Now it works.

Thanksss !!!

[kvbalakumar]

Hi Antonio,

Close the thread, since it is answered.

Regards,

Bala

[former_member312729]

Hi Antonio ,

Try this

SELECT T0.[CardCode], T0.[CardName],  sum(T1.[Quantity])[Quantity], sum(T1.LineTotal)[value]  FROM OINV T0  INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OCRD T2 ON T0.CardCode = T2.CardCode INNER JOIN OCRG T3 ON T2.GroupCode = T3.GroupCode WHERE T0.[DocDate] >=[%1] AND  T0.[DocDueDate] <=[%2]  AND  T3.[GroupType] = 'C' AND  T1.[TargetType] <>'14' Group by T0.[CardCode], T0.[CardName]

Regards:

Balaji.s

[kvbalakumar]

Try this,

SELECT 'Facturas', T0.CardCode, T0.CardName, T1.SlpName, T4.[GroupName], SUM(Quantity) as Unidades, SUM(LineTotal) as Euros
FROM OCRD T0 INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode INNER JOIN OINV T2 ON T0.CardCode = T2.CardCode INNER JOIN INV1 T3 ON T2.DocEntry = T3.DocEntry inner join OCRD T4 ON T0.GroupCode = T4.GroupCode WHERE T1.SlpName >=%0 AND T1.SlpName <=%1 AND T2.DocDueDate >=%2 AND T2.DocDueDate <=%3
GROUP BY T0.CardCode, T0.CardName, T1.SlpName, T4.[GroupName]
UNION
SELECT 'Abonos', T0.CardCode, T0.CardName, T1.SlpName, T4.[GroupName], SUM(Quantity *-1) as Unidades, SUM(LineTotal *-1) as Euros
FROM OCRD T0 INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode INNER JOIN ORIN T2 ON T0.CardCode = T2.CardCode INNER JOIN RIN1 T3 ON T2.DocEntry = T3.DocEntry  inner join OCRD T4 ON T0.GroupCode = T4.GroupCode WHERE T1.SlpName >=%0 AND T1.SlpName <=%1 AND T2.DocDueDate >=%2 AND T2.DocDueDate <=%3
GROUP BY T0.CardCode, T0.CardName, T1.SlpName, T4.[GroupName]

Regards,

Bala