Knowledgebase: Tutorials
[QODBC-Desktop] Troubleshooting - How Do I List the Subtotal of Invoices within Date Range
Posted by Jack - QODBC Support on 09 March 2016 12:27 PM

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

Problem Description:

I would like to do is list find 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 main customer, but it doesn't get me the combined subtotal of all the invoices for each job under that customer as well.

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

Solution:

You can use below query to get Subtotal of Invoices Within 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 SubTotal of all Invoices for Customer 2 and all the Customer 2 Jobs combined, then I would 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'

(0 vote(s))
Helpful
Not helpful

Comments (0)
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please complete the captcha below (we use this to prevent automated submissions).