Skip to Content
0
Former Member
Sep 26, 2008 at 07:39 PM

Simplify Query

31 Views

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