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!
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]'
Gordon Du replied
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