Query to show BP year to date sales minus credits

[former_member834429]

Hi Experts,

I would like a query which will show me a single line for each BP:

BP Code, Salesperson, BP Name, Sales Group, Sales YTD (ex vat)

I want the Sales YTD to be be calculated as total invoiced sales less any credits,

Thanks in advance for any help..

regards

Geoff

[Former Member · Accepted solution]

Hi Geoff,

Try:

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  T2.TargetType != 14 
And DateDiff(YY,T1.DocDate,GetDate()) = 0
GROUP BY T0.CardCode, T0.CardName, T3.SlpName
HAVING Sum(IsNull(T2.LineTotal,0))>0

Please clarify what the sales group is in your context.

Thanks,

Gordon

[former_member834429 · Accepted solution]

thanks guys - some good solutions. gordon - yours seemed to take less time to run so we are going with it. again many thanks

[jitin_chawla]

Hi,

Check this if it helps :

select t0.cardcode as 'Business Partner Code' ,t2.slpname as 'Sales Person Name',
t0.cardname as 'Business Partner Name', t0.GroupCode as 'Sales Group',
sum(t3.doctotal)- sum(t3.vatsum) as 'YTD Sales', 
sum(t4.doctotal) - sum(t4.vatsum) as 'YTD Credit Memo',
sum(t5.doctotal) as 'YTD Payments' 
from OCRD t0 
inner join JDT1 t1 on t0.cardcode = t1.shortname 
inner join OSLP t2 on t0.slpcode = t2.slpcode
left join OINV t3 on t3.objtype = t1.transtype and t3.docdate >= '2011.01.01' and t3.docdate <= '2011.12.31'
left join ORIN t4 on t4.objtype = t1.transtype and t0.slpcode = t4.slpcode and t4.docdate >= '2011.01.01' and t4.docdate <= '2011.12.31'
left join ORCT t5 on t5.transid = T1.transid and t5.docdate >= '2011.01.01' and t5.docdate <= '2011.12.31'
where 
cardtype = 'C' and t1.refdate >= '2011.01.01' and t1.refdate <= '2011.12.31'
group by t0.cardcode, t2.slpname,t0.cardname,t0.groupcode
order by t0.cardcode

Kind Regards,

Jitin

SAP Business One Forum Team

[Former Member]

Hi Geoff....

Try this.....

SELECT T0.[CardCode], T0.[CardName], T3.[SlpName], Sum(T2.[LineTotal]) 
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  T2.[TargetType] <>14 
And (T1.DocDate>='[%0]' and T1.DocDate<='[%1]') 
GROUP BY T0.[CardCode], T0.[CardName], T3.[SlpName]

Regards,

Rahul