DataBlox OfficeQ Queries

This section contains queries that involve employees, when they worked for the company, and associated payroll transactions and other information.

Within QuickBooks, each employee has a stored set of payroll earnings items and payroll deduction items. You can query the complete history of any of these items - both employee paid and company paid.

OfficeQ also extracts full payroll transaction data (which is not available in competing products). When the pEmployee table is combined with the aTranLine table, dozens of important payroll reports become possible. There are several examples of payroll earnings reports.

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: E - P11 - - Name: E110 Employee - Simplest (All fields)

This query outputs all employee fields

SELECT *

FROM pEmployee

ORDER BY sLastName, sFirstName

Group: E - P13 - - Name: E130 Employee - Simple - Field List

This query outputs an arbitrary list of fields from the pEmployee table. There are lots of other fields to add - depending on your needs

SELECT nKey, sName, sPostalCode,
  sPhone, sPhoneMobile, sSSAN,
  sEmplType, dHired, dReleased, bInactive

FROM pEmployee

ORDER BY sFullName

Group: EP - P13-mt - - Name: P135 Payroll - TransLines containing kPayItem field

Show all tranlines that have kPayItems. Only fields containing useful data (where the record contains a value kPayItem) are listed.

Sorting by sTranType and sPayItemType makes it easier to see how the fields are used.

SELECT nKey, dDate, sTranType, kAcct, sAcctType,
  mAmount, kEntity, sEntityType, kEntity2,
  sEntity2Type, rUnitPrice, kPayItem,
  sPayItemType, dPayThrough,
  mWageBase, mPayTaxable

FROM aTranLine

WHERE kPayItem IS NOT NULL

ORDER BY sTranType, sPayItemType

Group: E - P20 - - Name: P200 Payroll Trans - TranLine Fields: analysis

Lists all tran lines that identify an employee.

These are typically on type Paycheck and YtdAdjst

SELECT *

FROM aTranLine

WHERE sEntityType = 'Employee'

ORDER BY sTranType, kEntity, nLine

Group: E - P23 - - Name: E230 Employee - Simple, Stats

Outputs some fields from the pEmployee table. The first column is fabricated by concatenating ('glueing') together the last name and a space and the first name.

Last field calculates how many years each employee has been with the company, rounded off to the nearest tenth of a year (.1 year).

SELECT sFirstName + ' ' + sLastName,
   sSSAN, sEmplType,
   sGender, dHired,
   ROUND((NOW() - dHired) / 365.25, 1)
      AS Years_With_Company

FROM pEmployee

WHERE bInactive = False

ORDER BY ROUND((NOW() - dHired) / 365.25, 1) DESC

Group: E - P28-m - <50 more Name: E280 Employees - TranTypes Involved: analysis

Lists counts of each transaction type that involves an employee (typically only Check and Paycheck).

The second query also breaks out the type of pay item - such as HourlyRegular, Deduction, Tax, ...

SELECT
  sTranType,
  COUNT(sTranType) AS CntTranType

FROM aTranLine

WHERE
     sEntity2Type = 'Employee'
  OR sEntityType = 'Employee'

GROUP BY sTranType

------------------------------------------------
SELECT
  sTranType, sPayItemType,
  # COUNT(sTranType) AS CntTranType,
  COUNT(sPayItemType) AS CntTranPayItemType

FROM aTranLine

WHERE
     sEntity2Type = 'Employee'
  OR sEntityType = 'Employee'

GROUP BY sTranType, sPayItemType

Group: EP - P29 - Sc Name: P290 Payroll - Paychecks written to Employees

List all checks that were written to employees for payroll purposes - with the bank account used. These are normally the only transaction lines that do not reference a payroll item.

Note that this query does not actually display foreign keys (kAcct, kEntity, kClass) - but uses their values to look up the names of each instead.

SELECT sDocNum, dDate, sTranType,
   (SELECT sFullName FROM aAccount
    WHERE aAccount.nKey = kAcct)
    AS BankAccount, sAcctType,
   mAmount,
   (SELECT sName FROM pEmployee
    WHERE pEmployee.nKey = kEntity)
    AS EmployeeName, sEntityType,
   (SELECT sFullName FROM aClass
    WHERE aClass.nKey = kClass)
    AS Class,
   dPayThrough

FROM aTranLine

WHERE sTranType = 'Paycheck'
  AND kPayItem IS NULL

ORDER BY dDate

Group: E - P31 - - Name: E300 Employee - All Transactions

Lists all tran lines that identify an employee.

These are typically of type Paycheck and YtdAdjst - although other tran types are often seen.

Note that sorting by nKey implies sorting by when the transaction occurred (date & time) because nKey are in transaction occurrence sequence

SELECT pEmployee.sFirstName, pEmployee.sLastName,
  aTranLine.*

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

WHERE
     sEntityType = 'Employee'
  OR sEntity2Type = 'Employee'

ORDER BY kEntity, sTranType, aTranLine.nKey

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: EP - P41 - JiSUgRX Name: P410 Payroll - Earnings Report

This query will produce an earnings report for all employees during a specified time interval.

The CountAmount serves no purpose other than verifying that the count of transactions makes sense.

Notice that it only sums earnings for payroll items where sPayItemSort is less than 35. These are items that contribute to paychecks - such as salary and wages.

SELECT sName, sSSAN,
SUM(mAmount) AS SumAmount,
COUNT(mAmount) AS CountAmount

FROM pEmployee INNER JOIN aTranLine
ON pEmployee.nKey = aTranLine.kEntity2

WHERE kPayItem IN (
  SELECT nKey FROM pPayrollItem
  WHERE sPayItemSort < '30'
  )

  AND (dDate >= #1/1/2012#)
  AND (dDate <= #12/31/2012#)

GROUP BY sName, sSSAN

ORDER BY sName

--------------------------------------
WHERE (kPayItem IN (
  SELECT nKey FROM pPayrollItem
  WHERE sPayItemType
    IN ('Salary', 'Wage', 'Addition')))

Group: CEV - P52-s - Un Name: C520 All Entities - By Phone#

This query creates a list of the phone numbers of all customers, jobs, employees, and vendors - sorted by phone number.

It's main purpose is to show what a union query looks like - although it might be useful if you monitor caller ID! Note that the parts of each query following 'AS' are identical.

SELECT
  sPhone AS PhoneNumber,
  sEntityType AS CompanyType,
  sFullName AS Name,
  sBillCity AS City, sBillState AS State
FROM cCustJob
WHERE sPhone IS NOT NULL

UNION

SELECT
  sPhone AS PhoneNumber,
  'Vendor' AS CompanyType,
  sName AS Name,
  sCity AS City, sState AS State
FROM vVendor
WHERE sPhone IS NOT NULL

UNION

SELECT
  sPhone AS PhoneNumber,
  'Employee' AS CompanyType,
  sName AS Name,
  sCity AS City, sState AS State
FROM pEmployee
WHERE sPhone IS NOT NULL

ORDER BY PhoneNumber

Group: EP* - P72-ms - JlGrHa Name: E720 Employees - Transaction Statistics

This query shows a line for each active employee - with identifying fields, count of vacation and sick hours currently available.

In addition there are some transaction statistics. These include the date of their first and last transactions - as well as the average number of days between transactions.

SELECT
  pEmployee.nKey,
  pEmployee.sEmplType,
  sName,
  rVacHoursAvail,
  rSickHoursAvail,
  MIN(dDate) AS FirstTransDate,
  MAX(dDate) AS LastTransDate,
  COUNT(dDate) AS CountTrans,
  (LastTransDate - FirstTransDate) / CountTrans
    AS Avg_Days_Between_Docs

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

WHERE pEmployee.bInactive = False
  AND bDocMain = True

GROUP BY pEmployee.nKey, pEmployee.sEmplType,
sName,  rVacHoursAvail, rSickHoursAvail

HAVING COUNT(dDate) > 0

Group: EP - P75-dm - JiSuDl Name: P750 Payroll - Earnings Report Detail

This query produces detail lines that make up the earnings report for addition type payroll items (HourlyRegular, HourlyVac, HourlySick, SalaryRegular, SalaryVac, SalarySick, Addition) only.

This example also uses the DLookup feature of Access Jet - which is conceptually simple, but not portable to different database types. Adding a JOIN on table pPayrollItem would make it portable.

SELECT sName,
  dDate,
  DLookup("sPayItemType", "pPayrollItem",
    "nKey = " & kPayItem) As sPayItemType,
  DLookup("sName", "pPayrollItem",
    "nKey = " & kPayItem) As PayItem,
  mAmount

FROM pEmployee INNER JOIN aTranLine
ON pEmployee.nKey = aTranLine.kEntity2

where (kPayItem IN (
  SELECT nKey FROM pPayrollItem
  WHERE sPayItemSort < '35'
  ))

  AND sTranType = 'PayCheck'
  AND mAmount <> 0
  AND YEAR(dDate) in (2009, 2010, 2011,
                      2012, 2013, 2014)

ORDER BY sName, dDate