| |
|
|
Technical Support
|
|
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.
SELECT *
FROM pPayrollItem
ORDER by sPayItemSort, sName
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
SELECT COUNT(*) AS TotalCnt,
COUNT(kPayItem) AS HasPayItem,
((HasPayItem / TotalCnt) * 100) AS Percentage
FROM aTranLine
WHERE sTranType = 'Paycheck'
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
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
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
SELECT kDoc, nLine, dDate, kAcct, sAcctType,
mAmount, kEntity, sEntityType, vVendor.sName,
kPayItem, aTranLine.sPayItemType,
pPayrollItem.sName
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
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')))
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
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
TRANSFORM COUNT(*)
SELECT pPayrollItem.sPayItemType, sName
FROM aTranLine INNER JOIN pPayrollItem
ON aTranLine.kPayItem = pPayrollItem.nKey
GROUP BY sPayItemSort,
pPayrollItem.sPayItemType,
sName
PIVOT sTranType
SELECT
aTranLine.sTranType,
DLOOKUP(
"sFullName", "xEntity",
"nKey = " & kEntity2) AS EntityName,
aAccount.sAcctType,
aAccount.sFullName,
aTranLine.bNoPost,
aTranLine.kDoc,
pPayrollItem.sPayItemType,
pPayrollItem.sName AS PayItemName,
aTranLine.rItemCount,
aTranLine.rUnitPrice,
aTranLine.bUnitPricePcnt,
aTranLine.mAmount
FROM
(aAccount RIGHT JOIN aTranLine
ON aAccount.nKey = aTranLine.kAcct)
LEFT JOIN pPayrollItem
ON aTranLine.kPayItem = pPayrollItem.nKey
WHERE (kPayItem IS NOT NULL)
ORDER BY kDoc, nLine
# ORDER BY EntityName
--------------------------------------
AND (aTranLine.rUnitPrice IS NOT NULL)
TRANSFORM SUM(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")