Skip to Content
0
Apr 03, 2017 at 03:06 PM

Sales YTD by Sales Employee Query

606 Views

I have been trying to write a query on SAP B1 in order to show customer sales YTD by Sales Employee, as Sales Analysis doesnt break this down into one coherent unit, and I have gotten a bit stuck.

It is returning data but not correctly as it isnt showing teh totals YTD correct (I know this by running SA to compare) and I would also like to show the profit as well, but cant seem to get my head around showing that - any help would be greatfully received as I need to sort for our end of year sales meeting:

SELECT T0.CardCode, T0.CardName, T2.SlpName, Sum(IsNull(T1.LineTotal,0)) 'YTD Total'
FROM OINV T0
Left JOIN INV1 T1 ON T1.DocEntry = T0.DocEntry
Left JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode

WHERE T2.SlpName = [%0]
And DateDiff(YY,T0.DocDate,GetDate()) = 0

GROUP BY T0.CardCode, T0.CardName, T2.SlpName
HAVING Sum(IsNull(T1.LineTotal,0))>0

Regards
Roy Bright