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'}
| 
