Troubleshooting - How Do I List the Subtotal of Invoices within the Date Range

Problem Description:

I want to list the subtotal of all Invoices by a customer for not only that particular customer but all jobs assigned to that customer within a specified date range.

What QODBC SQL Statement would I use?

This is what I have so far:

SELECT SUM(Subtotal) AS gt FROM Invoice WHERE CustomerRefListId='30000-1166980720' AND (TxnDate>={d'2006-12-24'} AND TxnDate<={d'2007-01-23'})

But that will only get me the subtotal of invoices for the primary customer, but it doesn't get me the combined subtotal of all the invoices for each job under that customer.

Is there a way to combine it all into one SQL Statement? Or do I have to use multiple statements and then manually combine all the totals?

Solution:

You can use the below query to get the Subtotal of Invoices Within the Date Range using QODBC.

You can run this query:

SELECT Invoice.CustomerRefFullName, Sum(Invoice.SubTotal) AS SumOfSubTotal FROM Invoice WHERE (((Invoice.TxnDate)>={d'2007-01-01'})) and (((Invoice.TxnDate)<{d'2007-12-01'})) GROUP BY Invoice.CustomerRefFullName



You can change the greater than or equal (>=) and the less than (<) dates to the date range you wish to use.


If you want to get the SubTotal of all Invoices for Customer 2 and all the Customer 2 Jobs combined, I suggest you go straight to the QuickBooks reporting engine instead, which will add up and total things for you (including any credits). QuickBooks reports can be accessed using the QODBC stored report procedures:

sp_report SalesByCustomerSummary show Text as Customer, Label as Job, Amount_1 as Total parameters DateFrom = {d'2007-01-01'}, DateTo= {d'2007-12-01'}, SummarizeColumnsBy = 'TotalOnly'