| Excel Query that shows sales totals and billed hours. |
| Author |
Message |
| Vern |
| |
| Group | : Members |
| Posts | : 5 |
| Joined | : 2007-10-10 |
|
| Profile |
|
| Posted : 2007-10-10 02:28:33 |
| I work for an accounting firm and our sales are service related. I am working on a query that shows total sales for each client for the year 2007. I tried to query this info through time tracking. but the numbers do not match. Where do I retrieve the sales totals from correctly? |
|
|
|
| Tom |
 |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2007-10-10 08:45:09 |
| What QuickBooks report best shows the data you are looking for? |
|
|
|
| Vern |
| |
| Group | : Members |
| Posts | : 5 |
| Joined | : 2007-10-10 |
|
| Profile |
|
| Posted : 2007-10-10 22:38:28 |
| Sales by Customer Summary. But i would like the SELECT statement rather than sp_report. My goal is too link the time billed(sales) to the employee. |
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2007-10-11 07:36:11 |
Well this is a little harder than it should be. As far as finding the Billed Timesheets, there's no link in the InvoiceLine or InvoiceLinkedTxn tables to TimeTracking and on top of all that, timesheets are consolidated when invoiced.
See: Table Relations for TimeByName for more. |
|
|
|
| Vern |
| |
| Group | : Members |
| Posts | : 5 |
| Joined | : 2007-10-10 |
|
| Profile |
|
| Posted : 2007-10-11 10:54:54 |
Ok thankyou for the info. If i just wanted to pull the total sales for a given year (2007). what would the query looklike. I am trying something like the following
for excel.
SELECT Sales.CustomerRefFullName, Customer.ParentRefFullName, SUM(sales.Amount)
FROM Sales, Customer
WHERE Sales.CustomerRefListid=Customer.ListID AND (Sales.TimeCreated=>'2007-01-01') AND (Sales.TimeCreated=<'2007-12-31')
GROUP BY Sales.CustomerRefFullName, Customer.ParentRefFullName
I am wanting to pull the Parent Name to excel so i can group customers that have a parent name.
|
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2007-10-12 10:20:38 |
Look good to me.
SELECT Sales.CustomerRefFullName, Customer.ParentRefFullName, SUM(sales.Amount) as TotalSales
FROM Sales, Customer
WHERE Sales.CustomerRefListid=Customer.ListID
AND Sales.TimeCreated>={ts'2007-01-01 00:00:00.001'} AND Sales.TimeCreated<={ts'2007-12-31 23:59:59.999'}
GROUP BY Sales.CustomerRefFullName, Customer.ParentRefFullName
|
|
|
|
