Skip to Content
0
Former Member
Oct 01, 2008 at 05:34 PM

Question On YTD Total Sales Query

77 Views

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