m m
Technical Support
OfficeQ SQL Queries - Payroll

Payroll Items are items that either contribute to an employees paycheck (such as salary and hourly wages, bonuses), or items that deduct (such as different types of taxes, health insurance, etc.). There are also taxes that need to be paid by the company separate from the employee. The payroll subsystem manages all these amounts and consolidates them for paychecks, tax authority payments, sick and vacation time reports, etc.

The most useful queries in this section combine the pPayrollItem table with the aTranLine table. You can create several types of earning reports, tax reports, liability payment reports, etc. These queries can also be used to verify that the payroll subsystem is working properly and can be used to trace complete transaction history. Also, setup data (such as expense and liability accounts currently used - and used in the past) can be monitored.

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: P - P10 - -           Name: P100 Payroll Item - Simplest List
Show all fields for payroll items sorted by type of payroll item and name

FROM pPayrollItem

ORDER by sPayItemSort, sName
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


ORDER BY sTranType, sPayItemType
Group: P - P17-dm - scottlrn           Name: P170 Payroll - Paychecks With PayItem: analysis
Test to see if COUNT works as documented. The COUNT(kPayItem) should only count records where kPayItem is not null.

Shows percentage.

  COUNT(kPayItem) AS HasPayItem,
  ((HasPayItem / TotalCnt) * 100) AS Percentage

FROM aTranLine

WHERE sTranType = 'Paycheck'
Group: P - P20-d - Sc           Name: P200 Payroll - Accounts assigned to Each PayrollItem2
Displays which account(s) are assigned to each PayrollItem. Testing for the existance of account types can give additional information about the payroll item - specifically:

If a Tax item has values for both kAcctExp and kAcctLiab, then it's a company paid expense. If it has only a kAcctLiab account, then it's an empolyee paid expense. + other cases

SELECT pPayrollItem.nKey, pPayrollItem.sListID,
 pPayrollItem.sName, sPayItemType,
 sPayItemSort, pPayrollItem.bInactive,
 A.sFullName AS ExpAccount,
 A2.sFullName AS LiabAccount

FROM (aAccount AS A RIGHT JOIN pPayrollItem 
  ON A.nKey = pPayrollItem.kAcctExp)

  LEFT JOIN aAccount AS A2
  ON A2.nKey = pPayrollItem.kAcctLiab

WHERE pPayrollItem.nKey IS NOT NULL

ORDER BY sPayItemSort, pPayrollItem.sName

SELECT sPayItemType, sName,

  (SELECT sFullName FROM aAccount
   WHERE aAccount.nKey = kAcctExp)
   AS ExpenseAccount,

  (SELECT sFullName FROM aAccount
   WHERE aAccount.nKey = kAcctLiab)
   AS LiabAccount

FROM pPayrollItem

ORDER BY sPayItemType, sName
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,
   (SELECT sName FROM pEmployee
    WHERE pEmployee.nKey = kEntity)
    AS EmployeeName, sEntityType,
   (SELECT sFullName FROM aClass
    WHERE aClass.nKey = kClass)
    AS Class,

FROM aTranLine

WHERE sTranType = 'Paycheck'
  AND kPayItem IS NULL

Group: P - P38-m - Ji           Name: P380 Payroll Trans - List of all PayCheck tranlines
This is a raw transaction line query to show all information available about paycheck transactions. It uses a LEFT JOIN so that all aTranLine kPayItem values are displayed - even if NULL.

It's purpose is for education and generating ideas.

SELECT kDoc, nLine, dDate, kAcct, sAcctType,
  rUnitPrice, mAmount, kEntity, sEntityType,
  aTranLine.sPayItemType, sName, kPayItem,
  dPayThrough, mWageBase, mPayTaxable

FROM aTranLine LEFT JOIN pPayrollItem
  ON aTranLine.kPayItem = pPayrollItem.nKey

WHERE sTranType = 'PayCheck'

ORDER BY aTranLine.nKey
Group: P - P39-m - J3           Name: P390 Payroll - List of all liability payments
Generates a list of all payroll liability payments that have been made. Includes the vendor, account and the payroll item the payment is for.

SELECT kDoc, nLine, dDate, kAcct, sAcctType,
  mAmount, kEntity, sEntityType, vVendor.sName,
  kPayItem, aTranLine.sPayItemType,

FROM (vVendor INNER JOIN aTranLine
        ON vVendor.nKey = aTranLine.kEntity)
  INNER JOIN pPayrollItem
    ON pPayrollItem.nKey = aTranLine.kPayItem

WHERE sTranType = 'LiabPymt'

ORDER BY vVendor.sName, kDoc, nLine
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.

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#)



WHERE (kPayItem IN (
  SELECT nKey FROM pPayrollItem
  WHERE sPayItemType
    IN ('Salary', 'Wage', 'Addition')))
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.

  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

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.

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

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
Group: P - P75-d - PiJiGr           Name: P760 Payroll Item - TranType Counts
Show all trantypes that contain valid values in aTranLine.kPayItem. Displays the count of each payroll item / transaction type combination.

Uses a pivot with:
columns -> trantypes
rows -> payroll items
cells -> count of tranline

SELECT pPayrollItem.sPayItemType, sName

FROM aTranLine INNER JOIN pPayrollItem
  ON aTranLine.kPayItem = pPayrollItem.nKey

GROUP BY sPayItemSort,

PIVOT sTranType
Group: P - P77-m - J3           Name: P770 Payroll - TranLines w Pay Items: analysis
Outputs all tranlines that contain payroll items.

    "sFullName", "xEntity",
    "nKey = " & kEntity2) AS EntityName,
  pPayrollItem.sName AS PayItemName,
  (aAccount RIGHT JOIN aTranLine
  ON aAccount.nKey = aTranLine.kAcct)

  LEFT JOIN pPayrollItem
  ON aTranLine.kPayItem = pPayrollItem.nKey


ORDER BY kDoc, nLine
# ORDER BY EntityName

  AND (aTranLine.rUnitPrice IS NOT NULL)
Group: P - P83 - PiJiGr           Name: P830 Payroll - Payments Liability Payments
List all vendors with the total amount of liability payments they have received - by time period.

Can be done by month, quarter, or year.

cols: vendor name
rows: time periods
cell: sum of mAmount


SELECT YEAR(dDate) & '-' & FORMAT(dDate, 'mm')
  AS Month

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

WHERE aTranLine.sTranType = 'LiabPymt'

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

PIVOT vVendor.sName

  for quarterly:
YEAR(dDate) & '-Q' & FORMAT(dDate, "q")