Question On YTD Total Sales Query

[Former Member]

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

[former_member583013 · Accepted solution]

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

[Former Member]

The second one should be more accurate. When you required the BP address with the Invoice and Credit Memo difference within a same report, I know that might not be a proper way to do. It hardly can be done correctly just by a simple column to calculate all the necessary documents.

Thanks,

Gordon