Query TOP 20 Customers Sales

[Former Member]

Hi all, i have a issue with a query........i have this query...

SELECT TOP 20 X.CARDNAME,X.TOTAL1, ISNULL(W.TOTAL1,0) as TOT, 
(X.GRSSPROFIT-isnull(W.GRSSPROFIT,0)) AS GRSSPROFIT FROM
 
(SELECT DISTINCT CARDNAME,SUM(ISNULL(TOTAL1,0)) AS TOTAL1,
SUM(ISNULL(GRSSPROFIT,0)) AS GRSSPROFIT FROM(
SELECT distinct T0.cardname, sum(T1.[LineTotal]) AS Total1,
sum(T1.grssprofit) as grssprofit
FROM OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry 
WHERE T1.[BaseType]  '203' AND T0.[DocDate] >='20090501'  AND
  T0.[DocDate] <='20090531'
GROUP BY T0.cardname,T1.[LineTotal],T1.grssprofit
)A GROUP BY CARDNAME,TOTAL1,GRSSPROFIT ) X LEFT JOIN 
 
(SELECT DISTINCT CARDNAME,SUM(ISNULL(TOTAL1,0)) AS TOTAL1,
SUM(ISNULL(GRSSPROFIT,0)) AS GRSSPROFIT FROM(
SELECT distinct T0.cardname, sum(T1.[LineTotal]) AS Total1,
 sum(T1.grssprofit) as grssprofit 
FROM ORIN T0 INNER JOIN RIN1 T1 ON T0.DocEntry = T1.DocEntry  
WHERE T1.[BaseType] <> '203' AND T0.[DocDate] >='20090501'  AND
  T0.[DocDate] <='20090531'
GROUP BY T0.cardname,T1.[LineTotal],T1.grssprofit
)B GROUP BY CARDNAME,TOTAL1,GRSSPROFIT )W 
ON X.CARDNAME=W.CARDNAME Group by x.cardname, x.total1, w.total1, X.GRSSPROFIT,
w.GRSSPROFIT ORDER BY sum(x.total1 - w.total1) desc

Maybe its not nice but with this i want get TOP 20 of my Customers with better sales, but i dont know what happend ........this query showme a weird results.......sales that not true.....

Someone have a query for this or give me a hand for make work.....i know that maybe there is another way more easy to do that.....but my expertise in SQL is not much......

PS: I did of this way (query) because i need all invoice of determinate date all credit memo of same date, with this if exists an invoice out of range (date) that was cancelled i need to affect this report........i hope explain me correct

:S

[Former Member]

Try this one:

SELECT DISTINCT TOP 20 T0.CARDNAME,SUM(ISNULL(T1.[LineTotal\],0)) AS TOTAL1,

SUM(ISNULL(T1.GRSSPROFIT,0)) AS GRSSPROFIT

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

WHERE T1.[BaseType\] != '203' AND T0.[DocDate\] Between '20090501' AND '20090531'

GROUP BY T0.cardname

Order By SUM(ISNULL(T1.[LineTotal\],0)) DESC

Thanks,

Gordon

[Former Member]

Tnks for answer.......

Gordon, i have to subtract all credit memo to invoices......because if a invoice was cancell out of range of date i need to subtract too.

checking you query still show me total sales that its not true....

Example:

Customer: Jhon Doe Company

Invoice 0001 date: 04/30/2009 Tot: 400 <-was canceled 05/05/2009

Invoice 0002 date: 05/25/2009 Tot: 300

Invoice 0003 date: 05/28/2009 Tot: 200

Invoice 0004 date 05/30/2009 Tot: 100

So....when i run the query with range 05/01/2009 to 05/31/2009 that give me:

Jhon Doe Company tot: 600

But the Invoice 0001 was canceled in 05/05/2009 i need subtract 400 to 600

the correct way will be:

300

+200

+100

-

---

600

- 400

-

---

200

So:

the sales to John Doe Company was: 200 in the date range 05/01/2009 - 05/31/2009

SAP BO do the things in this way........

[Former Member]

Updated:

SELECT DISTINCT TOP 20 T0.CARDNAME,SUM(ISNULL(T1.LineTotal,0) - ISNULL(T2.LineTotal,0)) AS TOTAL1,

SUM(ISNULL(T1.GRSSPROFIT,0)-ISNULL(T2.GRSSPROFIT,0)) AS GRSSPROFIT

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

LEFT JOIN RIN1 T2 ON T2.BaseEntry = T1.DocEntry and T2.BaseLine = T1.Linenum

WHERE T1.BaseType != '203' AND T0.DocDate Between '20090501' AND '20090531' AND T0.CANCELED = 'N'

GROUP BY T0.cardname

Order By SUM(ISNULL(T1.LineTotal,0) - ISNULL(T2.LineTotal,0)) DESC

[Former Member]

With this query you subtract the possible credit memo but in the same range......is like: T1.targettype != '14'........

In other words you subtract credit memo only if proceed of a invoice in the same range......I want subtract credit memo even the invoice not be of the same date range.....

Invoice 0001 tot : 400 date: 04/30/2009 <- this was canceled 05/08/2009

Invoice 0002 tot : 300 date: 05/05/2009

Invoice 0003 tot : 200 date: 05/10/2009

Invoice 0004 tot: 100 date: 05/17/2009

credit memo 01 tot: 400 date 05/08/2009

So the query do:

check date range: 05/01/2009 to 05/31/2009

Invoice0002 300 - 0 ´0´ for no credit memo

Invoice0003 200 - 0

Invoice0004 100 - 0

- - - - -

Tot: 600

But this customer have a credit memo01 of 400 with date 05/08/2009 so is in the range 05/01/2009 to 05/31/2009 must be subtract, the invoice was in April but was canceled in May so must be check.

tot of invoices: 600

tot credit memo: 400 in range 05/01/2009 to 05/31/2009

tot final: 200

[msundararaja_perumal]

Hi,

Try this one in the sap query manager and see whether it satisfies you.

select * from (select distinct top 20 max(t1.docnum)'DocNo',max(t1.docdate)'Posting Dt.',max(t1.cardcode)'BP Code',max(t1.cardname)'BP Name',

sum(t1.doctotal)'InvoiceTotal',sum(t1.paidsum)'PaidAmt',t1.docstatus'DocStatus'

from oinv t1 inner join inv1 t2 on t1.docentry=t2.docentry where t2.targettype!=14

and t1.docdate between [%0] and [%1] group by t1.cardcode,t1.cardname,t1.docstatus)a

order by a.InvoiceTotal desc

[Former Member]

Tnks for you help and time........ but still have problems with the credit memos, maybe the simple way is:

Range 05/01/2009 to 05/31/2009

Of client X all invoices - all credit memos in that date range, the result will be sum(total)