|
Buy Support
Incidents |
|
If you can't find your answer
in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us
private or confidential information: |
|
Click Here
|
If you can't
login and post questions or you are having trouble viewing forum posts:
Click Here
|
Callback
Support |
|
If you live in USA, UK, Canada, Australia or New
Zealand, you can leave us details on your question and request us to call you back and discuss
them with you personally (charges apply). |
|
Click Here
|
Buy Support
Incidents |
|
If you can't find your answer
in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us
private or confidential information: |
|
Click Here
|
|
| How Do I List the Subtotal of Invoices Within Date Range |
| Author |
Message |
|
|
| Posted : 2007-01-24 15:22:42 |
Quick Question:
What 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 used?
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 in one SQL Statement? Or do I have to use multiple statements then manually combine all the totals together. Thanks. |
|
|
|
| Tom |
 |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2007-01-24 16:21:31 |
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
and change the greater than or equal (>=) and the less than (<) dates to the date range you wish to use. |
|
|
|
|
|
| Posted : 2007-01-24 16:47:08 |
Greetings,
Thank you.. that definitely helps and points me in the right direction but I need the information to be more concise than what you have included.
Take for example the following balance scenario's...
Customer 1 - $0
Job 1 - $0
Job 2 - $0
Customer 2 - $35 (value not assigned to particular jobs)
Job 1 - $10
Job 2 - $12
Job 3 - $11
Job 4 - $38
Job 5 - $80
How can I find the SubTotal of all Invoices for Customer 2 and all the Customer 2 Jobs combined? So in the above example, return the value of $186 which is the total of Customer 2 + Job 1 through 5
Thank you. |
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2007-01-24 23:01:37 |
In that case, 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'
|
|
|
|
|
|
| Posted : 2007-01-26 08:24:09 |
Thank you again for the help...
A follow up question. Your example returns the total for each and every customer within QuickBooks. Is it possible to return the total for only ONE customer (and all their jobs combined)?
In your example image, there is
Bristol, Sonya
Utility Shed 1999.00
Repairs 93.84
Total Bristol, Sonya 2092.84
Is it possible to simply return "Total Bristol, Sonya 2092.84"? I only need the total value for ONE customer at a time, not a single report containing all customers.
Thanks again for all your help! Definitely getting close to what I need! |
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2007-01-26 12:43:23 |
Yes, the will return just the Total Bristol, Sonya 2092.84 line:
sp_report SalesByCustomerSummary show Label as Job, Amount_1 as Total
parameters DateFrom = {d'2007-01-01'}, DateTo= {d'2007-12-01'},
SummarizeColumnsBy = 'TotalOnly',
EntityFilterFullNameWithChildren = 'Bristol, Sonya'
Where Label like 'Total %' |
|
|
|
|