Simplify Query

[Former Member]

Hello All --

We have this Query (see below) that gives us each invoice and credit memo posted for a select date range.

However, we would like a simplified version of this report. The report would still allow us to select the date range. Instead of showing each individual invoice and credit memo posted, it would summarize the total balance for that day's posting.

So, it would really just show: Posting Date, Net Balance (Total Invoices - Credit Memos). After allowing us to select the date range.

Hope this makes sense!

Thanks,

Mike

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

[Former Member · Accepted solution]

Hi Mike:

Please try:

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

Thanks,

Gordon