Daily Invoice Posting Summary Query

[Former Member]

Hello --

Question about this Query below:

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

Where T1.RefDate Between '[%0]' and '[%1]' and T2.CardType = 'C'

Group by T1.RefDate

Order By T1.RefDate

This Query allows us to view a summary of the invoices and credit memo's posted for a specified time frame.

I noticed that in the initial pop up window when we select this Query, it allows us to select the date range. Today the date range ended with 10/1 -- today's date.

However, after we select this range, the results show only up to 9/30 as the last date.

The results page is correct as the Invoices we have processed only apply up to 9/30 -- we have not invoiced for 10/1 yet. Why does the selection period show up to 10/1 and not 9/30?

Thanks!!

Mike

[former_member583013 · Accepted solution]

Mike,

By design when you use '[%0\]' to select a value, the system by default will display all the dates in the OJDT table. It does not filter the dates at this stage..it only display all the dates available in that table.field. There would be other Journal Entries on Oct 1st which is why this date is also shown.

Just take the following example

SELECT T0.CardCode, T0.CardName FROM [dbo\].[OCRD\] T0 WHERE T0.CardType = 'C' AND T0.CardCode = '[%0\]'

If you notice, though your query is intended to only to select CardType = 'C' denoting customers ...all cardcode including will be listed for your selection

[Former Member · Accepted solution]

Guys --

I'm still a little confused. Let me try to explain another way.

With this Query -- when we run it. The first window asks us to Define Survey Variables. When we click existing values, we generate a list of Dates with # of Records next to each date.

The last three records show:

9/30 - 569

10/1 - 199

10/2 - 72

If I select 9/30 and 10/1 as my range, it gives me the same value as when I select 9/30 and 10/2 as my range.

Even though 10/2 shows that there are 72 records present -- but when 10/1 is selected as the ending range it still provides the same values as when 10/2 is selected.

In either case there are only 2 records and the value for each record is the same.

I don't understand this.

Thanks,

Mike

[former_member583013 · Accepted solution]

Mike,

As I wrote to you in my earlier reply....what you see on the list of dates for selection is what is on the OJDT table.

9/30 - 569 records for this date

10/1 - 199 records for this date

10/2 - 72 records for this date

It does not mean that the 72 records that are found in OJDT match your criteria on belong to the document you are filtering on.

OJDT contains journal entries from every possible document in Business One.....

There are two things that happens when you select the date range 9/30 - 10/2

In your query the WHERE clause is what does the filtering and qualification of the results...in the below WHERE clause.....

Where T1.RefDate Between '[%0\]' and '[%1\]' is only one part...if you substitute them with the date...

Where T1.RefDate Between '09/30/08' and '10/02/08'...the total records returned would be 840 (total of the above date range)...then come the T2.CardType = 'C' filteration...in which no records from 10/02/08 qualify... and only those which qualify show in the result.

Where T1.RefDate Between '[%0\]' and '[%1\]' and T2.CardType = 'C'

Group by T1.RefDate

Order By T1.RefDate

I think this is the best one can explain.........

[Former Member · Accepted solution]

Suda -- okay, I get it now.

When the Query first prompts for the date range, I thought the records next to it were just Invoice / Credit Memo Postings - but looks like it is any possible document.

To help us avoid confusion, can we modify this Query so that the date selection does not include the records. Just the dates. We are really only interested in selecting the date range where there have been Invoices / Credit Memos posted.

Thanks, sorry for the confusion.

Mike

[former_member583013 · Accepted solution]

Mike,

As Gordon is suggesting using the TaxDate does not make any difference at all. In the Journal Entry table the RefDate and the TaxDate are going to be the same in most cases...you will still continue to see the same results ....

Also the query is missing the

Group by T1.RefDate

Order By T1.RefDate

which you may please add before you give it a try

Only if you link the Invoice table OINV with OJDT and accept the date parameter from OINV.DocDate would it work. Like the one below

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

Suda

[Former Member · Accepted solution]

Suda --

It worked very well -- so, just to clarify, this Query just pulls the Invoices and Credit Memos and lists the dates that these postings took place, right?

Thanks!!

Mike

[Former Member · Accepted solution]

Mike,

This query is checking Invoice date only.

Thanks,

Gordon

[former_member583013 · Accepted solution]

Mike,

I am using only the Invoice date for the Selecting criteria. I would be able to use only one date of either document and I chose Invoice Date.

Suda

[Former Member]

Hi Mike:

SELECT T1.TaxDate, 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
Where T1.TaxDate Between '[%0]' and '[%1]' and T2.CardType = 'C'
Group By T1.TaxDate
Order By T1.TaxDate

Check this one to see.

Thanks,

Gordon

[Former Member]

Hi Mike,

You need to select 9/30 instead of 10/1. Between in the query include the ending date.

Thanks,

Gordon