m m
Technical Support
m
OfficeQ SQL Queries - Customers / Jobs / Receivables

Queries that involve customers, jobs, accounts receivables, and customer related transactions are in this section. The cCustJob table contains a record for each customer or job with around 50 fields.

Transaction types that involve customers or jobs are (alphabetically): CardChrg, CashSale, Check, CustCred, CustInvc, CustPymt, InvtAdjst, and Journal.

Using the aTranLine table, you can join together any combination of criteria for creating customers lists. For example, using cCustJob and/or aTranLine, here are a few random query examples:

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: C - P09-d - -           Name: C090 Customer - Simple List: all fields
Selects all columns from all customer and jobs. Sorts the result by full name.

If the resulting row is a customer - the full name will be the customer name. If it is a job, the full name will be the customer name, followed by a colon, followed by the job name. Job names can also have additional levels of nesting, such as: Lyons:Rambler:Foundation 
SELECT *

FROM cCustJob

ORDER BY sFullName
Group: C - P13-x - -           Name: C130 Customer - Simple List ---resume XlsArgs
Displays simple list of customers and jobs, sorted by name - with receivables balance due for each.

Note that because they are sorted by full name - each customer is listed immediately before any job for that customer. Jobs for a particular customer are also listed alphabetically.

SELECT nKey, sFullName,
  sEntityType, sCompanyName,
  sFirstName, sLastName,
  sBillState, sShipState, mBalance

FROM cCustJob

ORDER BY sFullName
Group: C - P21 - -           Name: C210 Customer - Credit Available
This will list the available credit for each customer or job.

In order to calculate the available credit, the mCreditLimit field must have a valid non null value. Otherwise, the company or job is not output. If none of the CustJobs in the file have valid values for mCreditLimit - there will be no output 
SELECT sFullName, sEntityType,
  mCreditLimit, mBalance,
  mCreditLimit - mBalance AS Available

FROM cCustJob

WHERE mCreditLimit IS NOT NULL

ORDER BY sFullName
Group: C - P25 - X           Name: C250 Customer - Email, valid percent
Show the percentage of customers or jobs that have email addresses. This does not validate the email address - it only indicates that the field is not empty 
SELECT COUNT(sEmail) AS CountWithEmail,
  COUNT(*) AS CountCustomers,
  FORMAT((COUNT(sEmail) / COUNT(*)), "00.0%")
    AS PercentWithEmail

FROM cCustJob
Group: CT - P26-t - -           Name: C260 Receivables - Max Aging
Notice that this is the same set of transaction lines listed with the other A/R query, even though the queries are very different 
SELECT sAcctType, sTranType, nLine,
  sEntityType, sEntity2Type, dDate,
  dDue, mBalanceDue, mAmount

FROM aTranLine

WHERE sAcctType = 'AcctRec'
  AND sEntityType = 'CustJob'
  AND sTranType IN
    ('CustInvc', 'Check', 'CustPymt', 'CustCred')
  AND bNoPost = False

ORDER BY nKey
# ORDER BY sTranType, nKey
Group: CI - P28 - <50 more           Name: C280 Customers - Item Types: in sales documents
Lists all inventory type items that occur within the lines of customer centered documents.

Excludes bDocMain - but those lines will never have have field kItem anyway 
SELECT
  sTranType, sItemType,
  COUNT(sTranType) AS CntTranType,
  COUNT(sItemType) AS CntItemType

FROM aTranLine

WHERE
  sTranType IN
    ('CashSale','CustInvc','CustChrg','CustCred')
 AND
   kItem IS NOT NULL
 AND
   bDocMain = False
 AND
   sEntity2Type = 'CustJob'

GROUP BY sTranType, sItemType
ORDER BY sTranType, sItemType
Group: CZ - P30-d - Ji           Name: C300 Customers - TranLines: named subset of customers
Uses LIKE to only show transactions where the customer's name fits a pattern.


SELECT
  cCustJob.nKey,
  sFullName,
  cCustJob.sEntityType,
  sTranType,
  kEntity,
  mSumIncome,
  mSumExpense,
  mAmount
  
FROM
  cCustJob INNER JOIN aTranLine
    ON cCustJob.nKey = aTranLine.kEntity

WHERE cCustJob.sFullName LIKE 'A*'
   AND bDocMain = True
   AND bNoPost = False

ORDER BY cCustJob.sFullName, aTranLine.kDoc
Group: C - P32 - Jl           Name: C320 Customer - By Type
Generate a list of all customers and jobs - sorted by customer type, then customer or job full name.

Note that the full table.fieldname needs to be specified for the ORDER BY clause because both tables have fields named sFullName. Using the AS alias only affects the naming of the output columns 
SELECT
  cCustType.sFullName AS CustType,
  cCustJob.sFullName AS CustName,
  sEntityType AS EntityType
  
FROM cCustJob LEFT JOIN cCustType
  ON cCustJob.kCustType = cCustType.nKey

ORDER BY cCustType.sFullName, cCustJob.sFullName
Group: C - P36 - Gr           Name: C360 Customers - Zip vs City Counts
Verify accuracy of zip codes to cities and states in all customer oriented TranDocs. This is a quick way to assess the quality of data - or find out where your customers are 
SELECT
  sPostalCode,
  sCity,
  sState,
  COUNT(*) as CountZips

FROM aTranDoc

WHERE sTranType IN
    ('CustInvc', 'Check', 'CustPymt', 'CustCred')

GROUP BY sPostalCode, sCity, sState

ORDER BY sPostalCode
Group: C - P38-msv - JlGr           Name: C380 Customers - Sales Volume: with first and last dates
List of all customers or jobs by sales volume. Sums all aTranLine mAmount fields by sTranType - including only entries that post to Income or OthIncome type accounts.

Also lists the date of the first bill and the last bill for that customer.

Could be sorted by either the customer name or dollar volume.

SELECT
  sFullName,
  cCustJob.sEntityType,
  -SUM(mAmount) AS SalesVolume,
  MIN(dDate) AS FirstTrans,
  COUNT(*) AS Count_Of_Trans,
  MAX(dDate) AS LastTrans

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

WHERE bNoPost = False
  AND
    (aTranLine.sAcctType = 'Income'
    OR 
     aTranLine.sAcctType = 'OthIncome')

GROUP BY sFullName, cCustJob.sEntityType

ORDER BY sFullName

----------------------------------------
ORDER BY -SUM(mAmount) DESC
Group: AC - P38-st - JiSu           Name: C385 Receivables - By Account Type: analysis
Output all tranLines that post to accounts receivable type accounts for customers or jobs that currently owe money. Positive dollar amounts are invoices - negative are payments. Both invoices and payments for each customer or job are sorted by date.

Useful for understanding A/R transactions. Note that most of the tranlines should be from type CustInvc or CustPymt transactions. Occasionally Check or CustCred will be seen. Any other transaction types should be suspect. Also, payments do not necessarily correspond directly with invoices. Sometimes payments are for a portion of an invoice - and sometimes a single payment is for multiple invoices. The aTranLink table needs to be used to connect payment chunks with invoices.

Note that this query uses both an inner join and a subquery. It could have also been done with a 3 table join.

SELECT cCustJob.sFullName, sAcctType,
  dDate, sTranType, nLine, aTranLine.sEntityType,
  dDate AS dSold, dDue, mAmount

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

WHERE (aTranLine.kAcct IN (
  SELECT nKey
  FROM aAccount
  WHERE sAcctType = 'AcctRec'))

  AND bNoPost = False
  AND mBalance > 0

ORDER BY sFullName, dDate
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: C - P40-wv - JiGr           Name: C400 Customers - Sale Stats: by CustJob
Lists each customer or job / trans type / item type combination - with count of sale line items and sum of dollar amounts for those items. Covers all transactions - although date filtering could be added.

Item type groups the line items by Service, NonInvtPart, InvtPart, TaxItem, OthCharge, etc.

SELECT
  sFullName, sTranType, sItemType,
  COUNT(*) AS cnt_saleslines,
  -SUM(mAmount) AS sum_mAmount

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

WHERE
  sTranType IN
  # sTranType NOT IN
    ('CashSale','CustInvc','CustChrg','CustCred')
 AND
   bDocMain = False
 AND
   sEntity2Type = 'CustJob'

GROUP BY sFullName, sTranType, sItemType
Group: C - P41 - JlGrHa           Name: C410 Customer - Unused
Lists all customers or jobs that have never had a transaction.

This works because all tran lines have a valid value in dDate. Could be used to delete extraneous customers or jobs.

Alternate form could be used to list only customers or jobs that do have transactions.

Note that there are often customers with no transactions that are entered so jobs can be attached to them 
SELECT
  sFullName,
  cCustJob.sEntityType,
  COUNT(dDate) AS CountOfTrans

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

GROUP BY sFullName, cCustJob.sEntityType

HAVING COUNT(dDate) = 0

-------------------------------------------
HAVING COUNT(dDate) > 0
Group: CV - P44-?dm - Ji           Name: C440 Receivables Analysis
Displays the relationship between the following fields:

TranDoc.sFlags (F flag = paid in full)
TranLine.dPaidInFull (date if fully paid)
TranLine.mStillOwing (money amount still owing)

These only exist for trantypes: CustChrg, CustInvc, and VendBill.

Note that only the main TranLine for this TranDoc (the one that has bDocMain = True) will have the dPaidInFull and mStillOwing fields.

The test for mAmount <> 0 is to eliminate voided transactions.

SELECT aTranDoc.dDate,
  aTranDoc.sTranType,
  aTranDoc.sFlags,
  aTranLine.kAcct,
  aTranLine.sAcctType,
  aTranLine.kEntity,
  aTranLine.sEntityType,
  aTranLine.mAmount,
  aTranLine.dPaidInFull,
  aTranLine.mStillOwing

FROM aTranDoc INNER JOIN aTranLine
ON aTranDoc.nKey = aTranLine.kDoc

WHERE
  (aTranLine.dPaidInFull IS NOT NULL
    OR
  aTranLine.mStillOwing > 0)

ORDER BY aTranDoc.sTranType

#  aTranLine.bDocMain = True
#    AND
#  aTranLine.mAmount <> 0
#    AND
#  bNoPost = False

----------------------------------------------
    AND
  aTranLine.sTranType IN
    ('CustChrg', 'CustInvc', 'VendBill')

  ((aTranDoc.sFlags LIKE '*F*')
    OR
   (aTranLine.dPaidInFull IS NOT NULL)
    OR
   (aTranLine.mStillOwing <> 0))
Group: AC* - P51-S - JiSuGr           Name: C500 Receivables vs. Tranlines: integrity
Verifies that the mBalance (amount they owe) field of each customer or job matches the sum of transactions.

The Difference column should be $0.00 for each. Any other value signifies a problem in the A/R system for that customer.

This is a complex, but very important query. It could have also been done with a 3 table join and would probably execute more quickly.

SELECT cCustJob.nKey,
  sFullName,
  mBalance,
  SUM(aTranLine.mAmount) AS SumOfmAmount,
  mBalance - SUM(aTranLine.mAmount) AS Difference,
  COUNT(aTranLine.mAmount) AS CountOfmAmount

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

WHERE (aTranLine.kAcct IN (
    SELECT nKey
    FROM aAccount
    WHERE sAcctType = 'AcctRec' ))
  AND
    aTranLine.bNoPost = False

GROUP BY cCustJob.nKey, sFullName, mBalance

ORDER BY sFullName
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: 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: C - P65<-dmx - JlGrHa           Name: C650 Customers - Sales Volume, Total: recursive 

SELECT
  sFullName,
  cCustJob.sEntityType,
  -SUM(mSumIncome) AS SalesVolume

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

WHERE bNoPost = False
  AND
    aTranLine.bDocMain = True

GROUP BY sFullName, cCustJob.sEntityType
HAVING -SUM(mSumIncome) <> 0

ORDER BY sFullName


----------------------------
ORDER BY -SUM(mSumIncome) DESC
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: C - P74-ms - PiJiGr           Name: C740 Customers - Gross Profit: per time period
For each CustJob - the gross profit made during each time period. This can be easily modified for other time periods too.

TRANSFORM -SUM(mSumIncome) - SUM(mSumExpense)

SELECT cCustJob.sFullName,
  cCustJob.sEntityType

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

WHERE YEAR(dDate) IN (2009, 2010, 2011, 2012, 2013)
  AND bNoPost = False
  AND bDocMain = True

GROUP BY cCustJob.sFullName,
  cCustJob.sEntityType

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: CI* - P77-mSt - J3Gr           Name: C770 Customers - Services Prices charged
Lists all cCustJobs that bought particular types of services. Shows the service item, total count bought by that customer, and average price charged for that customer - sorted in that order.

This can be useful for both seeing how well items are selling as well as what pricing has been charged to different customers. Note that (typically) with some items, the price is fairly consistent while with others it varies widely.


SELECT
  vInvtItem.sItemType,
  vInvtItem.sFullName AS ServiceName,
  cCustJob.sFullName,
  -SUM(aTranLine.rItemCount) AS TotalUnits,
  FORMAT(AVG(aTranLine.rUnitPrice), "$#,##0.00")
    AS AvgUnitPrice
  
FROM
  (cCustJob INNER JOIN aTranLine
    ON cCustJob.nKey = aTranLine.kEntity2)
  INNER JOIN vInvtItem
    ON aTranLine.kItem = vInvtItem.nKey

WHERE aTranLine.sItemType IN ("Service")

GROUP BY
  vInvtItem.sItemType,
  vInvtItem.sFullName,
  cCustJob.sFullName

ORDER BY vInvtItem.sFullName,
  CDBL(FORMAT(AVG(aTranLine.rUnitPrice), "0.00")),
  cCustJob.sFullName
Group: CI* - P79-m - J3Gr           Name: C790 Customers - Items Sold: groupby item
Lists all inventory like items sold to a particular subset of customers. In this case, it's all customers whose sFullName starts with 'C'.

Change the criteria in the WHERE clause as needed.

SELECT
  cCustJob.nKey,
  cCustJob.sFullName,
  cCustJob.sEntityType,
  vInvtItem.sFullName,
  vInvtItem.sItemType,
  -SUM(aTranLine.rItemCount) AS TotalUnits,
  -SUM(aTranLine.rItemCount * aTranLine.rUnitPrice)
    AS TotalPrice,
  FORMAT(TotalPrice / TotalUnits, "0.00")
    AS AveragePrice
  
FROM
  (cCustJob INNER JOIN aTranLine
    ON cCustJob.nKey = aTranLine.kEntity2)
  INNER JOIN vInvtItem
    ON aTranLine.kItem = vInvtItem.nKey

WHERE cCustJob.sFullName LIKE 'C*'
  AND
    vInvtItem.sItemType IN
      ("InvtPart", "NonInvtPart")
  AND aTranLine.bNoPost = False

GROUP BY
  cCustJob.nKey,
  cCustJob.sFullName,
  cCustJob.sEntityType,
  vInvtItem.sFullName,
  vInvtItem.sItemType

ORDER BY cCustJob.sFullName, vInvtItem.sFullName
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: C - P85-sv - PiJlGr           Name: C810 Customers - Sales Volume: by time period
Show all customers or jobs along with quarterly sales for that customer or job for each included quarter. The time period could be adjusted (for example to months or years) and the years to be included could be adjusted.

Note that this only shows income - not expenses.

TRANSFORM -SUM(mSumIncome)

SELECT
  sFullName,
  cCustJob.sEntityType
  
FROM cCustJob LEFT JOIN aTranLine
ON cCustJob.nKey = aTranLine.kEntity

WHERE bNoPost = False
  AND aTranLine.bDocMain = True
  AND
    YEAR(dDate) BETWEEN 2005 AND 2018
  AND
    mSumIncome <> 0

GROUP BY sFullName, cCustJob.sEntityType

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

------------------------------
    YEAR(dDate) IN (2005, 2006, 2007, 2008,
      2009, 2010, 2011, 2012, 2013)
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')