Question On YTD Total Sales Query
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]'
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?
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.