Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

Viewing Total Cumulative Purchases For BP Customer

Hello,

Is there any way that we can add a column to the below Query that shows the total cumulative YTD sales for each account?

In the Query below, the Balance column shows current outstanding balance on the account. However, this is different than cumulative sales and would really help us if we could gauge how much the customer has purchased from us within the past year.

If possible, can we put a column with this information at the end of the report -- with header 'YTD Sales'

Thanks!!

Mike

SELECT T0.CardCode 'Acct #', T0.CardName Company, T0.Address

' Address', T0.City ' City', T0.State1 State, T0.ZipCode

'Billing Zip', T0.Phone1 Phone, T0.Balance ' Balance',

T1.SlpName 'Sales Rep',

T2.PymntGroup Terms, T3.GroupName 'Group'

FROM OCRD T0

LEFT JOIN OSLP T1 ON T1.SlpCode = T0.SlpCode

LEFT JOIN OCTG T2 ON T2.GroupNum = T0.GroupNum

LEFT JOIN OCRG T3 ON T3.GroupCode = T0.GroupCode

WHERE T0.CardType = 'C'

replied

Mike,

Current month's Sales query

SELECT T0.CardCode 'Acct #', T0.CardName Company, T0.Address
' Address', T0.City ' City', T0.State1 State, T0.ZipCode
'Billing Zip', T0.Phone1 Phone, T0.Balance ' Balance',
T1.SlpName 'Sales Rep',
T2.PymntGroup Terms, T3.GroupName 'Group', ((SELECT ISNULL(SUM(INV1.LINETOTAL),0) 
FROM INV1 INNER JOIN OINV ON INV1.DocEntry = OINV.DocEntry 
WHERE OINV.CardCode = T0.CardCode AND 
Month(OINV.DocDate) = Month(GetDate()))-(SELECT 
ISNULL(SUM(RIN1.LINETOTAL),0) FROM RIN1 INNER JOIN 
ORIN ON RIN1.DocEntry = ORIN.DocEntry 
WHERE ORIN.CardCode = T0.CardCode AND Month(ORIN.DocDate) = Month(GetDate()))) [Monthly Sales] 
FROM OCRD T0
LEFT JOIN OSLP T1 ON T1.SlpCode = T0.SlpCode
LEFT JOIN OCTG T2 ON T2.GroupNum = T0.GroupNum
LEFT JOIN OCRG T3 ON T3.GroupCode = T0.GroupCode
WHERE T0.CardType = 'C'

0 View this answer in context

Helpful Answer

by
Not what you were looking for? View more on this topic or Ask a question