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'
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'