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