I'm going to start with showing what TimeByName does. In QuickBooks when I run the Time By Name report I see this:
When I use this TimeByName stored procedure in QODBC I see the same result:
sp_report TimeByName show Text, Label, DurationHours_1 parameters EntityFilterFullNames = 'Dan T. Miller', DateFrom = {d'2007-10-01'}, DateTo={d'2007-12-15'}
If I expand on Cook, Brian:Kitchen in QuickBooks (by double clicking on Cook, Brian:Kitchen in the Time By Name report) I see this:
If I query the TimeTracking table like this for Cook, Brian:Kitchen , I see the same records using QODBC:
SELECT TxnDate as Date, ItemServiceRefFullName as Item, IsBilled as "Billing Status", (DurationMinutes /60.00) as Hours FROM TimeTracking where EntityRefFullName = 'Dan T. Miller' and CustomerRefFullName = 'Cook, Brian:Kitchen' and TxnDate >= {d'2007-10-01'} and TxnDate <= {d'2007-12-15'}
In QuickBooks when I go to Invoice the 'Cook, Brian:Kitchen' job, I find the two unbilled items shown as:
By linking in the ItemService table I can find the rate and the total amount to be billed too!
SELECT TimeTracking.TxnDate as Date, TimeTracking.ItemServiceRefFullName as Item, TimeTracking.IsBilled as "Billing Status", (TimeTracking.DurationMinutes /60.00) as Hours, ItemService.SalesOrPurchasePrice as Rate, (TimeTracking.DurationMinutes /60.00 * ItemService.SalesOrPurchasePrice) as ToBeBilled FROM TimeTracking, ItemService where TimeTracking.ItemServiceRefFullName=ItemService.FullName and TimeTracking.EntityRefFullName = 'Dan T. Miller' and TimeTracking.CustomerRefFullName = 'Cook, Brian:Kitchen' and TimeTracking.TxnDate >= {d'2007-10-01'} and TimeTracking.TxnDate <= {d'2007-12-15'}
As far as finding the Billed Timesheets, there's no link in the InvoiceLine or InvoiceLinkedTxn tables to TimeTracking and in my case the timesheets have been consolidated. But I did find the (3 x 8 hours) =24 hours of Framing and the (6 x 8 hours) = 48 hours of Installation by doing:
SELECT TxnDate as Date, CustomerRefFullName as Name, RefNumber as Ref, InvoiceLineItemRefFullName as Service, InvoiceLineQuantity as Qty, InvoiceLineRate as Rate, InvoiceLineAmount as Amount FROM InvoiceLine unoptimized Where CustomerRefFullName='Cook, Brian:Kitchen' and (InvoiceLineItemRefFullName='Framing'or InvoiceLineItemRefFullName='Installation') and InvoiceLineQuantity > 8 and TxnDate >= {d'2007-10-01'}
|