DataBlox OfficeQ Queries

These queries involve jobs or employee time tracking. Even though jobs have fields identical to customers and each job has a customer as a parent - the focus here is on employee time tracking and time billing - and not financial (such as receivables).

Each record in the pTimeLine table is for a block of time - with info as to when it occurred, how long it was for, the customer the service was provided for, the employee or vendor who performed the service and what the actual service (inventory item) was - plus other fields.

There are queries for unbilled blocks of time, 'useage' queries for employee time, queries by customer, cross queries that intersect employee time, customer time, and service item, and any of the above separated by date boundaries.

The queries listed below are generally arranged from simple to complex. Normally, queries that get their data from only 1 table and do not aggregate any fields are listed first. To get full benefit of these queries and create your own queries - you should understand joins (where multiple tables are combined into a single table) and aggregation (where numeric columns from multiple rows are combined into 1 column). These concepts are not as difficult as they first appear and a web search for 'SQL' and 'join' or 'aggregation' will bring up hundreds of tutorials.

Where a line of dashes (like: --------------------) appears in a query, a useful variation of the query is shown. It could be very simple, such as changing the sort order of the output - or more complex, such as a complete alternate query. Where a single line in the query has the character: # as it's leftmost character - that line is a comment and is not processed by our query runner.

Group: J - P15 - - Name: M050 TimeLine - Simple Dump

Simple dump of all time line records - ordered by date, employee or vendor who provided the service, and the customer or job the service was performed for. This type of query is often useful to run first to get familiar with the data.

Fields bToBill and bBilled at the right end are no longer used. All the information they provided is now in field sBillStatus. The old fields still exist for compatability purposes.

SELECT *

FROM pTimeLine

ORDER BY dDate, kEmplVend, kCustJob

# ORDER BY nKey

Group: J - P17 - Gr Name: M180 TimeLines - Count By BillStatus

Count of TimeLines in each billing status.

This is about as simple of an aggregate query as is possible to write

SELECT sBillStatus,
  COUNT(*) AS Count_BillStatus

FROM pTimeLine

GROUP BY sBillStatus

Group: J* - P27-m - AX Name: M270 TimeLines By Month

List count of time line entries per month - from the first month to the last month containing time lines in the file.

SELECT
  YEAR(dDate) & '-'
    & FORMAT(dDate, "mmm") as Month,
  COUNT(*) as Count_TimeLines

FROM pTimeLine

GROUP BY
  YEAR(dDate) & '-' & FORMAT(dDate, "mm"),
  YEAR(dDate) & '-' & FORMAT(dDate, "mmm")

ORDER BY YEAR(dDate) & '-' & FORMAT(dDate, "mm")

Group: IJ - P33-dm - JiGr Name: M330 TimeLines - InvtItems / Time: analysis

Lists all 'inventory items' (actually types of work - typically services) that have been used in time lines. For each, lists the item type, name, total count of times the service was performed, total hours the service has been performed, etc.

Note that the inventory item type is almost always 'Service' - but is occasionally something else ('OthCharge').

SELECT sItemType,
  sFullName,
  COUNT(*) AS CountOfBlocks,
  SUM(rHours) AS TotalHours,
  FORMAT(TotalHours / CountOfBlocks, "0.0")
    As AverageHoursPerBlock

FROM vInvtItem INNER JOIN pTimeLine
  ON vInvtItem.nKey = pTimeLine.kItem

GROUP BY sItemType, sFullName

----------------------------------------
SELECT sItemType,
  sFullName,
  COUNT(*) AS CountOfBlocks,
  SUM(rHours) AS TotalHours,
  FORMAT(TotalHours / CountOfBlocks, "0.0")
    As AverageHoursPerBlock

FROM vInvtItem INNER JOIN pTimeLine
  ON vInvtItem.nKey = pTimeLine.kItem

WHERE sBillStatus = 'Billable'

GROUP BY sItemType, sFullName

Group: EJ - P37-dms - is null Name: M350 TimeLines - No CustJob: by Employee

Where employee time is logged - but not against a particular customer or job, list each employee with the count of time blocks and total of hours.

SELECT sName,
  COUNT(*) AS CountOfShifts,
  SUM(rHours) AS SumOfHours

FROM pEmployee INNER JOIN pTimeLine
  ON pEmployee.nKey = pTimeLine.kEmplVend

WHERE kCustJob IS NULL
GROUP BY sName

Group: CJ - P39-S - JlGrHa Name: C390 Customers - Stats, Dates, Counts

Shows all customers and jobs (down) that have transactions posted - sorted by name.

For each, shows (across):
full name, city, state
whether Customer or Job
FirstDate (date of 1st transaction)
LastDate (date of last transaction)
total count of TranDocs posted

Note that instead of querying the TranDocs table we use TranLines where nLine=0 because there is one of these for each corresponding TranDoc.

Could be restricted to a time span using a WHERE clause.

SELECT
  sFullName, sBillCity, sBillState,
  cCustJob.sEntityType,
  MIN(dDate) AS FirstDate,
  MAX(dDate) AS LastDate,
  COUNT(dDate) AS CountTrans

FROM cCustJob INNER JOIN aTranLine
ON cCustJob.nKey = aTranLine.kEntity

WHERE nLine = 0

GROUP BY sFullName, sBillCity, sBillState,
  cCustJob.sEntityType

HAVING COUNT(dDate) > 0

Group: J - P40-vst - J3 Name: M400 TimeLine - Activities To Bill: by CustJob

List all time line activities that have not yet been billed. For each, display the date the activity was performed, customer name, activity name (actually an inventory item), and billing fields.

Activity is always an inventory item of type 'Service'.

Field sBillStatus contains all bill status info needed. Fields bToBill and bBilled are for compatability with older versions.

SELECT pTimeLine.dDate,
  cCustJob.sFullName,
  vInvtItem.sItemType, 
  vInvtItem.sFullName,
  pTimeLine.rHours,
  sBillStatus

FROM
  (pTimeLine INNER JOIN cCustJob
    ON pTimeLine.kCustJob = cCustJob.nKey)
  INNER JOIN vInvtItem
    ON  pTimeLine.kItem = vInvtItem.nKey

WHERE sBillStatus = 'Billable'

ORDER BY cCustJob.sFullName

--------------------------------------------
ORDER BY pTimeLine.dDate

Group: CJ - P55-ms - <10 JlGr add margin Name: C550 Customers - Gross Profit

For each customer or job - list the total revenue, expenses, gross profit, and profit margin.

Note that expenses only include expenses directly attributable to that customer or job.

For generating other similar queries, note that the mSumIncome and mSumExpense fields only exist in aTranLine records that have field bDocMain set True.

SELECT
  sFullName,
  cCustJob.sEntityType,
  COUNT(*) AS Count_Of_Sales,
  -SUM(mSumIncome) AS SumIncome,
  SUM(mSumExpense) AS SumExpense,
  SumIncome - SumExpense AS Gross_Profit

FROM cCustJob LEFT JOIN aTranLine
ON cCustJob.nKey = aTranLine.kEntity

WHERE bNoPost = False
  AND
    aTranLine.bDocMain = True

GROUP BY sFullName, cCustJob.sEntityType

Group: CJ - p65-ds - <10 PiGr Name: M650 TimeLine - Pivot Unbilled Hours

Builds a table showing all unbilled hours in a particular year.

Creates a total of the unbilled hours for each job / employee combination or job / vendor combination. Shows the job, whether an employee or vendor performed the work, and the total count of hours for that combination.

I would be easy to break the totals into more categories - adding (for instance) what the actual service performed was.

TRANSFORM SUM(rHours)

SELECT
  cCustJob.sFullName AS Job,
  xEntity.sEntityType AS EmplOrVend,
  xEntity.sFullName AS EmployeeOrVendName

FROM ((pTimeLine INNER JOIN cCustJob
    ON pTimeLine.kCustJob = cCustJob.nKey)
  INNER JOIN xEntity
    ON xEntity.nKey = pTimeLine.kEmplVend)

WHERE pTimeLine.sBillStatus = 'Billable'
  AND YEAR(dDate) IN (2013)

GROUP BY
  cCustJob.sFullName,
  xEntity.sEntityType,
  xEntity.sFullName

PIVOT YEAR(dDate) & '-' & FORMAT(dDate, 'mm')

Group: J - P72-es - PiJ5Gr Name: M720 TimeLine - NotBillable Hours

Shows count of hours of work performed where payroll hours or payable vendor time was accrued - but not billable to any customer, by quarter.

If field EmplOrVend is Employee, the PayItemName will show the payroll item used to pay the employee. If EmplOrVend is Vendor - the PayItemName will be blank.

There are hundreds of queries possible using the time billing features of QuickBooks.

TRANSFORM SUM(rHours)

SELECT
  xEntity.sEntityType AS EmplOrVend,
  xEntity.sFullName AS EmplVendName,
  cCustJob.sFullName AS CustJobName,
  vInvtItem.sFullName AS Service,
  pPayrollItem.sName AS PayItemName

FROM
  ((((pTimeLine LEFT JOIN cCustJob
    ON cCustJob.nKey = pTimeLine.kCustJob)
  LEFT JOIN xEntity
    ON xEntity.nKey = pTimeLine.kEmplVend)
  LEFT JOIN vInvtItem
    ON vInvtItem.nKey = pTimeLine.kItem)
  LEFT JOIN pPayrollItem
    ON pPayrollItem.nKey = pTimeLine.kPayItem)

WHERE sBillStatus = 'NotBillable'

GROUP BY
  xEntity.sEntityType,
  xEntity.sFullName,
  cCustJob.sFullName,
  vInvtItem.sFullName,
  pPayrollItem.sName

PIVOT YEAR(dDate) & '-Q' & FORMAT(dDate, 'q')

Group: CJ* - P76-St - PiJiGr Name: M755 Hours by Job, Employee, Month

Shows total hours worked by employees or vendors on different jobs.

Uses Excel pivot to display each job as a row on the left - with a subrow for each employee that has worked on the job. The hours for each employee are shown - as well as the total hours for the job.

With minimal additions, this query could be constrained to a particular timeframe, class, physical location, etc.

SELECT dDate,
  cCustJob.sFullName AS CustJobName,
  xEntity.sEntityType & ' : ' & xEntity.sFullName
    AS EmplVendName,
  rHours

FROM
  ((pTimeLine LEFT JOIN cCustJob
    ON cCustJob.nKey = pTimeLine.kCustJob)
  LEFT JOIN xEntity
    ON xEntity.nKey = pTimeLine.kEmplVend)

ORDER BY cCustJob.sFullName,
  xEntity.sEntityType & ' : ' & xEntity.sFullName,
  dDate

Group: CJ - P80 - PiJiGr Name: M600 TimeLine - Pivot Unbilled Hours: customer

Builds table with months as columns, kCustJob as rows - with total hours of activities that should be billed but haven't been yet.

TRANSFORM SUM(rHours)

SELECT cCustJob.sFullName

FROM pTimeLine INNER JOIN cCustJob
ON pTimeLine.kCustJob = cCustJob.nKey

WHERE pTimeLine.sBillStatus = 'Billable'
  # AND YEAR(dDate) IN (2013, 2014, 2015)

GROUP BY cCustJob.sFullName

PIVOT YEAR(dDate) & '-' & FORMAT(dDate, 'mm')

Group: CJ* - P85-ms - PiJiGr Name: M610 TimeLine - Pivot rHours month, job

Builds table with months as columns, kCustJob as rows - with total hours worked during that month in each cell.

Edit WHERE to constrict dDate to specified years.

TRANSFORM SUM(rHours)

SELECT cCustJob.sFullName

FROM pTimeLine INNER JOIN cCustJob
ON pTimeLine.kCustJob = cCustJob.nKey

# WHERE YEAR(dDate) IN (2012, 2013, 2014)

GROUP BY cCustJob.sFullName

# PIVOT YEAR(dDate) & '-' & FORMAT(dDate, 'mm')
PIVOT YEAR(dDate) & '-Q' & FORMAT(dDate, 'q')