YTD Sales Figures

[Former Member]

I would like to get a list of customers by salesperson including their YTD sales.  Since I want address, city, state, zip and some other fields in the output, I am not able to use the sales analysis report.  After creating this query, I am going to export the results to an Excel spreadsheet.  I have created this query but do not know how to get the YTD sales figure for this business partner.  Is there an easy way to get this number?  

[KennedyT21 · Accepted solution]

Hi Caroline Stewart

Try This

SELECT T0.CardCode, T0.CardName, T3.SlpName, Sum(IsNull(T2.LineTotal,0)) 'YTD Total'

FROM OCRD T0

Left JOIN OINV T1 ON T0.CardCode = T1.CardCode

Left JOIN INV1 T2 ON T1.DocEntry = T2.DocEntry

Left JOIN OSLP T3 ON T0.SlpCode = T3.SlpCode

WHERE T0.CardType ='C'

And DateDiff(YY,T1.DocDate,GetDate()) = 0

GROUP BY T0.CardCode, T0.CardName, T3.SlpName

HAVING Sum(IsNull(T2.LineTotal,0))>0

Regards

Kennedy

[Former Member]

Hello Kennedy,

I was able to insert part of your query into mine and make it work.  Thanks!  However, my fiscal year is August 1 to July 31 and I am interested in the YTD totals being in that time frame.  Can you tell me how to modify it?

Thank you.

Caroline

[KennedyT21]

Hi Caroline Stewart

Check with the below condition

T1.DocDate >='20120701' And DateDiff(YY,T1.DocDate,GetDate()) = 0

Regards

Kennedy

[Former Member]

Here's my query so far:

SELECT T0.[CardCode], T0.[CardName], T1.[Street], T1.[City], T1.[State], T1.[ZipCode], T2.[SlpName], T3.[descript]

FROM OCRD T0 

INNER JOIN CRD1 T1 ON T0.CardCode = T1.CardCode

INNER JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode

INNER JOIN OTER T3 ON T0.Territory = T3.territryID

WHERE T0.[CardType] = 'C' AND  T1.[AdresType] = 'S' AND   T2.[SlpName] =[%1]

Thank you

Caroline

[KennedyT21]

Hi Caroline Stewart

Have You checked out the above query...

Regards

Kennedy

[kothandaraman_nagarajan]

Hi Caroline Stewart,

Please post query here to add YTD sales figure.

Thanks & Regards,

Nagarajan