Sales Summary Query

[Former Member]

Hello All --

We use this Query below to get a summary of Invoice / Credit Memo Postings each day. However, noticed that the numbers have changed when we checked today compared with last time -- maybe a week ago.

Shouldn't these numbers stay the same! Please advise why the numbers are different now than before. These are for sales generated in Sept.

Thanks!

Mike

SELECT T1.RefDate, SUM(T0.Debit - T0.Credit) as 'Net Balance'

from JDT1 T0

Inner Join OJDT T1 ON T1.TransId = T0.TransID and

(T1.TransType = '13' or T1.TransType = '14')

Inner Join OCRD T2 ON T2.CardCode = T0.ShortName

INNER JOIN OINV T3 ON T3.TransId = T1.TransId

Where T3.DocDate Between '[%0]' and '[%1]' and T2.CardType = 'C'

Group by T1.RefDate

Order By T1.RefDate

[former_member583013 · Accepted solution]

Mike,

Many business as I have seen back date their transactions for some business reason or the other. In that case the totals that you see on your report on one day could be different than another as a user could have changed the posting date on the transaction.

Suda

[Former Member · Accepted solution]

Guys --

I understand what you are saying but this does not appear to be the case - the differences are too frequent and large. The query below:

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

is what we use to get the detailed posting -- each individual posting for that date.

The summary query -- shown at the beginning of this thread -- should just summarize the Doc Total of this above Query. When I look at the Doc Total of individual selected dates in the above Query, I found that they do not match the Summary Query.

So, something is definitely wrong with the Summary -- please advise!!

Thanks,

Mike

[Former Member · Accepted solution]

Please try this:

SELECT T1.RefDate, SUM(T0.Debit - T0.Credit) as 'Net Balance' 
from JDT1 T0
Inner Join OJDT T1 ON T1.TransId = T0.TransID and
(T1.TransType = '13' or T1.TransType = '14')
Inner Join OCRD T2 ON T2.CardCode = T0.ShortName
LEFT JOIN OINV T3 ON T3.TransId = T1.TransId
LEFT JOIN ORIN T4 ON T4.TransID = T1.TransId
Where (T3.DocDate Between '[%0]' and '[%1]' or T4.DocDate
 Between '[%0]' and '[%1]') and T2.CardType = 'C' 
Group By T1.RefDate
Order By T1.RefDate

Thanks,

Gordon

[former_member583013 · Accepted solution]

Getting the totals from the document rather than the journal entry is what you should look for comparison.

In the query you have at the begining of the thread..the date referenced in the parameter should be RefDate and not DocDate

SELECT T1.RefDate, SUM(T0.Debit - T0.Credit) as 'Net Balance' 
from JDT1 T0
Inner Join OJDT T1 ON T1.TransId = T0.TransID and
(T1.TransType = '13' or T1.TransType = '14')
Inner Join OCRD T2 ON T2.CardCode = T0.ShortName
INNER JOIN OINV T3 ON T3.TransId = T1.TransId
Where T1.RefDate Between '[%0]' and '[%1]' and T2.CardType = 'C' 
Group by T1.RefDate
Order By T1.RefDate

[Former Member · Accepted solution]

Suda - tried your Query and it was still pulling up the same numbers that I had originally -- these numbers did not match the detailed Query posting Query.

Gordon -- looks like your summary did match the detailed posting query so will go ahead with that one.

However, would like to understand why there was a difference to begin with? Gordon and Suda, what are the differences between the Query that you guys just sent over?

Thanks!

Mike

[Former Member · Accepted solution]

This query is time consuming to check. I have some time today so that I could fine tone it by actual data.

If you found anything wrong, let me know.

Thanks,

Gordon

[Former Member · Accepted solution]

Okay - thanks Gordon, yes would like to know why there is a difference in the queries to make sure we are in fact using the right numbers in determining our sales.

Thanks,

Mike

[Former Member]

Follow up question above.

[Former Member]

No. It can not be the same. Unless you only post all your documents as today's (current) date.

Thanks,

Gordon

[Former Member]

Not sure, why can't it be the same? These were postings done in Sept. Why would they change?

Mike

[Former Member]

SInce the posting can occur to any previous dates, that is why the balance for the history could be changed. Only when you lock up the period, it can stay the same.