Skip to Content

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

Question On YTD Total Sales Query

Hello,

We use this query below to see YTD sales for each of our BP Customers:

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 Year(INV1.DocDate) = Year(GetDate()))-(SELECT ISNULL(SUM(RIN1.LINETOTAL),0)

FROM RIN1 INNER JOIN ORIN ON RIN1.DocEntry = ORIN.DocEntry

WHERE ORIN.CardCode = T0.CardCode AND Year(RIN1.DocDate) = Year(GetDate()))) [YTD 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'

We use this query below to see daily Invoice and Credit Memo postings for a selected period:

SELECT 'INVOICE' as "Doc Type", T0.DOCNUM as "Doc Number", T0.CARDCODE as "Customer Code", T0.CARDNAME as "Customer Name", T0.DOCDATE as "Posting Date", T0.NUMATCARD as "Customer Ref #", T0.DocDueDate, T0.DocTotal

FROM [dbo].[OINV] T0 WHERE T0.DOCDATE BETWEEN '[%0]' And '[%1]'

UNION ALL

SELECT 'CREDIT MEMO', T0.DOCNUM,T0.CARDCODE, T0.CARDNAME, T0.DOCDATE, T0.NUMATCARD, T0.DocDueDate, -1*T0.DocTotal

FROM [dbo].[ORIN] T0 WHERE T0.DOCDATE BETWEEN '[%0]' And '[%1]'

My question is -- shouldn't the sum of the YTD column in the 1st Query be the same as the sum of the Doc Total column in the 2nd Query (given that all dates are selected in the 2nd Query)?

This doesn't appear to be the case and I was wondering why?

Thanks,

Mike

replied

Mike,

The DocTotal may contain Freight and Handling expenses, While the first query only taken the SUM of the line total of the Items.

Thats why they may be different.

Suda

0 View this answer in context
Not what you were looking for? View more on this topic or Ask a question