DataBlox OfficeQ Queries

These queries involve vendors, types of vendors, accounts payables, items purchased from vendors and vendor returns. When vendors are used to perform services for customers that are facilitated by the company, this area also included those details. It also includes payments made to tax authorities as a result of payroll transactions.

Transaction types that involve vendors are (alphabetically): CardChrg, Check, ItemRcpt, Journal, LiabPymt, TaxPymt, VendBill, VendCred, and VendPymt.

You can create very detailed historical accounts payable reports. When combined with inventory items, you can create any type of purchase history needed. These queries can also be used to collect information about services performed by vendors.

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: V - P13 - - Name: V130 Vendor - Simple List

Simple query that shows the most basic info about each vendor - sorted by the vendor's name

SELECT sName, sCompanyName, sFirstName,
  sMidName, sLastName, sCity, sState,
  sContact, sPhone, sEmail, bInactive

FROM vVendor

ORDER BY sName

Group: V - P21 - - Name: V210 Vendor - Credit Available

List the available credit for each vendor which is calculated by subtracting their mBalance from their credit available.

In order to calculate the available credit, both the mCreditLimit and mBalance fields must have valid values. If either of these fields is null for any record, the line is not output.

Notice that balances are negative for vendors because of they are liabilities

SELECT sName,
  mCreditLimit AS CreditLimit,
  -mBalance AS Balance,
  (mCreditLimit - (-mBalance)) AS CreditAvailable

FROM vVendor

WHERE mCreditLimit IS NOT NULL
  AND mBalance IS NOT NULL

ORDER BY sName

Group: V - P25 - - Name: V250 Vendors - Count Valid Email

Show the percentage of vendors 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 TotalVendorss,
  FORMAT((COUNT(sEmail) / COUNT(*)), "00.0%")
    AS PercentWithEmail

FROM vVendor

Group: V - P29 - Jl Name: V290 Vendor - Type and Name

Simple join to show all vendor types with the vendors belonging to each type.

Using LEFT JOIN ensures that any vendor types without vendors are also listed.

SELECT
  vVendType.sFullName AS VendorTypeName,
  vVendor.sName AS VendorName

FROM
  vVendType LEFT JOIN vVendor
    ON vVendType.nKey = vVendor.kVendType

ORDER BY vVendType.nReportSort,
  vVendType.sFullName,
  vVendor.sName

Group: V - P30-eSt - JiGrX Name: A300 Account Expenses by Weekday

This is a somewhat off beat query that shows expenses by weekday. For many accounts, it has no meaning but for some accounts it can provide good insight.

Again it's easy to modify it for certain account types (such as credit cards), or certain expense accounts (such as Gasoline).

SELECT
  YEAR(dDate) AS Year,
  CHOOSE(FORMAT(dDate, "w"),
    "Monday", "Tuesday", "Wednesday", "Thursday",
    "Friday", "Saturday", "Sunday")
    AS DayOfWeek,
  aAccount.sFullName,
  SUM(aTranLine.mAmount) AS TotalDollars
  
FROM
  aAccount INNER JOIN aTranLine
    ON aAccount.nKey = aTranLine.kAcct

WHERE YEAR(dDate) IN (2012, 2013)
  AND bNoPost = False
  AND aAccount.sAcctType = "Expense"

GROUP BY
  YEAR(dDate),
  FORMAT(dDate, "w"),
  aAccount.sFullName

ORDER BY
  YEAR(dDate),
  FORMAT(dDate, "w")

Group: V - P30 - JlGrHa Name: V300 Vendor - Unused

Lists all vendors that have never had a transaction.

This works because all tran lines have a valid value in dDate.

Could be used to delete extraneous vendors.

SELECT
  sName, sState,
  COUNT(dDate) AS Count_Of_Trans

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

GROUP BY sName, sState

HAVING COUNT(dDate) = 0

Group: V - P39 - JiGrHa Name: V390 Vendors - Stats, Dates, Counts

Shows all vendors that have transactions posted - sorted by the count of transactions. Could be used to clean up the QuickBooks file by consolidating vendors that have a small count of transactions.

Most files have several vendors that were used for 1 transaction and could be easily consolidated to simplify the file.

For each, shows (across):
name, city, state
FirstDate (date of 1st transaction)
LastDate (date of last transaction)
total count of trandocs posted

Could be restricted to a time span using a WHERE clause

SELECT
  sName, sCity, sState,
  MIN(dDate) AS FirstDate,
  MAX(dDate) AS LastDate,
  COUNT(dDate) AS CountTrans

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

WHERE bDocMain = True

GROUP BY sName, sCity, sState

HAVING COUNT(dDate) > 0

# ORDER BY sName
ORDER BY COUNT(dDate)

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: V - P45 - JlSu Name: V459 Vendor - Count Trantypes

This lists all transaction types, with count of each - where a vVendor nKey is the kEntity.

If you add the OR ...kEntity2..., the query will add transaction types where vendors are involved in the transaction - but are not the center of the transaction.

Possible transaction types: CardChrg, CashSale, Check, CustCred, CustInvc, CustPymt, InvAdjst, Journa

SELECT aTranDoc.sTranType,
  COUNT(*) AS TranTypeCount

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

WHERE
  aTranLine.bDocMain = True
    AND
  aTranLine.bNoPost = False
    AND
  aTranLine.kEntity IN (SELECT nKey FROM vVendor)

GROUP BY aTranDoc.sTranType

------------------------------------------------
    OR
  aTranLine.kEntity2 IN (SELECT nKey FROM cCustJob)

Group: V - P50-s - JiGrHa Name: V500 Payables - Balance vs. Tran: integrity

Verifies that the mBalance (amount owed to) field of each vendor matches the sum of transactions.

The Difference column should be $0.00 for each.

SELECT vVendor.nKey,
  sName, mBalance,
  SUM(mAmount) AS SumOfmAmount,
  mBalance - SUM(mAmount) AS Difference,
  COUNT(mAmount) AS CountOfmAmount

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

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

GROUP BY vVendor.nKey, sName, mBalance

ORDER BY sName

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: V - P77-s - J3Gr Name: V770 Vendor - Items Bought

List all vVendors from which we purchased particular types of items. Show the item and total count purchased.

Note that kEntity2 is used because this is the primary tran doc key for the tran line - which will always be a vendor.

SELECT
  vVendor.sName,
  vInvtItem.sFullName,
  vInvtItem.sItemType,
  SUM(aTranLine.rItemCount) AS TotalUnits
  

FROM
  (vVendor INNER JOIN aTranLine
    ON vVendor.nKey = aTranLine.kEntity2)
  INNER JOIN vInvtItem
    ON aTranLine.kItem = vInvtItem.nKey

WHERE aTranLine.sItemType
  IN ("InvtPart", "NonInvtPart")

GROUP BY
  vVendor.sName,
  vInvtItem.sFullName,
  vInvtItem.sItemType

ORDER BY vVendor.sName, vInvtItem.sFullName

Group: V - P86-m - J3Gr Name: V861 Vendors - Vendor Expenses By Account

Using a list of accounts (or vendors in the 2nd query) - generate a list of the other that is posted to. Helps to find misclassified deductions, etc.

Lets you see how expenses to accounts or vendors are allocated with the ability to compare different years.

SELECT
  aAccount.nReportSort,
  aAccount.sAcctType,
  aAccount.sFullName,
  YEAR(dDate) AS Year,
  vVendor.sName,
  SUM(aTranLine.mAmount) AS TotalDollars
  
FROM
  (vVendor INNER JOIN aTranLine
    ON vVendor.nKey = aTranLine.kEntity2)
  INNER JOIN aAccount
    ON aTranLine.kAcct = aAccount.nKey

WHERE vVendor.sName
    IN ("Amazon", "Costco Wholesale",
        "Fred Meyer", "Lowe's", "NewEgg",
        "Paypal", "Target", "Walmart")
  AND YEAR(dDate) IN (2010, 2011, 2012)
  AND bNoPost = False

GROUP BY
  aAccount.nReportSort,
  aAccount.sAcctType,
  aAccount.sFullName,
  YEAR(dDate),
  vVendor.sName

Group: V - P86 - J3Gr Name: V862 Vendors - Vendors Expense By Vendor

Very similar to the prior query - except the output is arranged by vendor instead of account

SELECT
  vVendor.sName,
  YEAR(dDate) AS Year,
  aAccount.nReportSort,
  aAccount.sAcctType,
  aAccount.sFullName,
  SUM(aTranLine.mAmount) AS TotalDollars
  
FROM
  (vVendor INNER JOIN aTranLine
    ON vVendor.nKey = aTranLine.kEntity2)
  INNER JOIN aAccount
    ON aTranLine.kAcct = aAccount.nKey

WHERE vVendor.sName
    IN ("Amazon", "Costco Wholesale",
        "Fred Meyer", "Lowe's", "NewEgg",
        "Paypal", "Target", "Walmart")
  AND YEAR(dDate) IN (2010, 2011, 2012)
  AND bNoPost = False

GROUP BY
  vVendor.sName,
  YEAR(dDate),
  aAccount.sAcctType,
  aAccount.sFullName,
  aAccount.nReportSort

ORDER BY
  vVendor.sName,
  YEAR(dDate),
  aAccount.nReportSort

Group: V - P93-m - J4 Name: V930 Vendor - Items Billed or Billable

Shows list of all items or services purchased that are marked as not being NotBillable on the purchase document. This means the item is either Billable or HasBeenBilled. <<

SELECT
  cCustJob.sFullName AS CustJob,
  aTranLine.dDate,
  aTranLine.mAmount,
  vVendor.sName AS Vendor,
  vInvtItem.sFullName AS Item,
  vInvtItem.sItemType,
  aTranLine.sTranType,
  aTranLine.sBillableStatus


FROM
  ((vVendor INNER JOIN aTranLine
    ON vVendor.nKey = aTranLine.kEntity2)
  INNER JOIN vInvtItem
    ON aTranLine.kItem = vInvtItem.nKey)
  INNER JOIN cCustJob
    ON aTranLine.kEntity = cCustJob.nKey

WHERE aTranLine.sBillableStatus <> 'NotBillable'

ORDER BY CustJob, dDate

----------------------------------------------
WHERE aTranLine.sBillableStatus = 'Billable'
WHERE aTranLine.sBillableStatus IS NOT NULL