DataBlox OfficeQ Queries

This section contains queries that involve inventory and fixed assets. Inventory 'items' include many types of items that are not intuitively obvious - such as services, tax items, and groups. Fixed assets are stored along with inventory items - although they have substantially more fields.

There are a lot of example queries in this section. Examples include reports of all items on hand, all accounts assigned to inventory items, service items used in time lines, all items currently on order sorted by due date, comparison of on hand count vs. sum of adding or deleting transactions for each stocked item.

An inventory item will be one of 12 types:

"Service", "InvtPart", "InvtAssy", "NonInvtPart", "FixedAsset", "OthCharge", "SubTotal", "Group", "Discount", "Payment", "TaxItem", "TaxGroup"

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: I - P07-d - - Name: I070 Inventory - Simplest

Show a list of all inventory items, sorted first by the type of item - then by the name of the item.

Note that there are several types of inventory item (such as Service, Group, Subtotal) that are not actually inventory items in the normal sense - but are treated that way by QuickBooks because they are the basis for line items on documents.

SELECT *

FROM vInvtItem

ORDER BY sItemSort, sItemType, sFullName

Group: I - P10 - - Name: I100 Inventory - Trans Involving

Selects all transactions that involve inventory parts.

Items of type 'InvtPart' are the only type of inventory items for which on-hand counts are maintained

SELECT *

FROM aTranLine

WHERE sItemType = 'InvtPart'

ORDER BY kDoc, nLine

Group: I - P11 - - Name: I110 Inventory - Simple List

Selects all fields from all active inventory items.

You can also specifically list any subset of the fields.

The resulting list is sorted in the same order as QB

SELECT *

FROM vInvtItem

WHERE bInactive = False

ORDER BY sItemSort, sFullName

Group: I - P12-m - - Name: I120 Fixed Asset - Simple List

Simple list of fixed assets. Lists a few fields. Sorts the list by the date the item was purchased.

Note that typically a fixed asset 'item' consists of multiple units.

SELECT nKey, sFullName, sManufPart,
  dPurch, dSold, sLocation, sSerialNumber,
  dWarrExpire,
  FORMAT(rCostBasis, "0.00") AS CostBasis
   
FROM vInvtItem

WHERE sItemType = "FixedAsset"

ORDER BY dPurch

Group: I - P17-dm - Gr Name: I170 Inventory - ItemType Count

Lists the count of each type of inventory item.

SELECT
  sItemType,
  COUNT(sItemType) as Count_ItemType

FROM vInvtItem

GROUP BY sItemType
ORDER BY COUNT(sItemType) DESC

Group: I - P18-d - - Name: I185 Inventory - Trans, Fields Used

These are diagnostic queries useful for understanding how fields are used when an inventory item is present in the tranline.

The first query extracts all fields that are used when field sItemType has any value.

The second query (and other queries like it) can be used to test various conditions.

These queries are somewhat abstract, but are useful in learning details.

SELECT dDate, sTranType, kDoc,
  nLine, bDocMain, sDocNum,
  kAcct, sAcctType, mAmount, bNoPost,
  kEntity, sEntityType, kEntity2, sEntity2Type,
  kItem, sItemType, rItemCount, rUnitPrice,
  bUnitPricePcnt, kClass, bAutoTax, kSalesTax,
  rCountMisc, sMemo80, sBillableStatus, dService

FROM aTranLine
WHERE sItemType IS NOT NULL
ORDER BY dDate, sTranType, kDoc, nLine

---------------------------------------
# these should always be NULL or 0
# so they were deleted from list above
SELECT * FROM aTranLine
WHERE kItem IS NOT NULL
  AND (mBalanceDue <> 0
  OR mStillOwing <> 0
  OR dPaidInFull IS NOT NULL
  OR kPayMeth IS NOT NULL
  OR kPayItem IS NOT NULL
  OR sPayItemType IS NOT NULL
  OR dPayThrough IS NOT NULL
  OR mWageBase <> 0
  OR mPayTaxable <> 0
)

Group: I - P20-ms - - Name: I200 Inventory - Count and Value: on hand

Shows current on hand count of all inventory parts - as well as costs and price. Also calculates profit margin based on the average cost of items actually purchased.

Note that rEnteredCost is the cost manually entered for the item - where as rAverageCost is the calculated (from purchase history) average cost.

Negative values for rOnHandCount often indicate missing purchase transactions or vendor bills.

SELECT sFullName, sItemType,
 rOnHandCount,
 FORMAT(rEnteredCost,"0.00") AS EnteredCost,
 FORMAT(rAverageCost, "0.00") AS AverageCost,
 FORMAT(rUnitPrice, "0.00") AS UnitPrice,
 FORMAT(((rUnitPrice-rAverageCost)/rUnitPrice*100),
  "0") & "%" As ProfitMargin,
 FORMAT(rOnHandCount * rAverageCost, "0.00")
   AS OnHandValue,
 FORMAT(rOnOrder, "0.00") AS OnOrder,
 FORMAT(rOnSalesOrder, "0.00") AS OnSalesOrder

FROM vInvtItem

WHERE
    sItemType = 'InvtPart'
  AND rOnHandCount IS NOT NULL
  AND rAverageCost > 0
  AND rUnitPrice > 0

ORDER BY sFullName

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: IJ - P33-dm - JiGr Name: M330 TimeLines - InvtItems / Time: analysis

Lists all 'inventory items' (actually types of work - typically services) that have been used in time lines. For each, lists the item type, name, total count of times the service was performed, total hours the service has been performed, etc.

Note that the inventory item type is almost always 'Service' - but is occasionally something else ('OthCharge').

SELECT sItemType,
  sFullName,
  COUNT(*) AS CountOfBlocks,
  SUM(rHours) AS TotalHours,
  FORMAT(TotalHours / CountOfBlocks, "0.0")
    As AverageHoursPerBlock

FROM vInvtItem INNER JOIN pTimeLine
  ON vInvtItem.nKey = pTimeLine.kItem

GROUP BY sItemType, sFullName

----------------------------------------
SELECT sItemType,
  sFullName,
  COUNT(*) AS CountOfBlocks,
  SUM(rHours) AS TotalHours,
  FORMAT(TotalHours / CountOfBlocks, "0.0")
    As AverageHoursPerBlock

FROM vInvtItem INNER JOIN pTimeLine
  ON vInvtItem.nKey = pTimeLine.kItem

WHERE sBillStatus = 'Billable'

GROUP BY sItemType, sFullName

Group: I* - P39-dt - J3 Name: I390 Inventorys - TranLines, All: analysis

Outputs all tranlines that contain inventory items.

This is a simple query - useful for learning data or analysis. Can also restricted to certain tran types. Adding a WHERE clause or changing the sort order can be useful for finding specific items.

SELECT
    aAccount.sFullName,
    aAccount.sAcctType,
    aTranLine.sTranType,
    aTranLine.bNoPost,
    aTranLine.kDoc,
    aTranLine.nLine,
    aTranLine.sLineRoles,
    vInvtItem.sFullName,
    vInvtItem.sItemType,
    aTranLine.mAmount
FROM
  (aAccount RIGHT JOIN aTranLine
  ON aAccount.nKey = aTranLine.kAcct)

  INNER JOIN vInvtItem
  ON aTranLine.kItem = vInvtItem.nKey

ORDER BY kDoc, nLine

---------------------------------------
add?: WHERE aTranLine.sTranType = 'CustInvc'

Group: I - P43-s - Ji Name: I430 Inventory - Individual Item Summary

List all transactions involving a single inventory item. This allows you to trace all transactions that involve a particular inventory item - regardless of transaction type.

You can either enable or disable the line that includes 'bNoPost' entries (by including / excluging the # symbol). If enabled, the output will include transaction lines (such as Estimate) that don't actually affect in-stock quantities.

The item's sFullName or nKey must be correctly entered before the query is executed - otherwise the query will return no results.

Ordering by kDoc also orders by dDate because kDocs are always in date (and time) order.

SELECT
  dDate,  sTranType,  kDoc,  nLine,
  sFullName,  rItemCount,
  aTranLine.rUnitPrice,  mAmount,
  bNoPost,  sLineRoles

FROM vInvtItem INNER JOIN aTranLine
  ON vInvtItem.nKey = aTranLine.kItem

WHERE vInvtItem.sFullName = 'Wood Door:Exterior'
  # AND bNoPost = False

ORDER BY kDoc, nLine

-----------------------------------------
WHERE vInvtItem.nKey = 22

Group: I - P44 - Ji Name: I440 Inventory - On Order: by date due

Produces a list of all inventory items that are currently on order, but have not yet been received.

Sorts the list by the name of the inventory item and the date due.

Also shows how much you are paying for each item

SELECT
  sFullName,
  dDue,
  rItemCount,
  aTranLine.rUnitPrice

FROM vInvtItem INNER JOIN aTranLine
  ON vInvtItem.nKey = aTranLine.kItem

WHERE aTranLine.sTranType = 'PurchOrd'
  AND aTranLine.bReceived = False
  AND aTranLine.bDocMain = False

ORDER BY dDue

Group: I - P50-dms - JiGr Name: I500 Inventory - Count vs Tran: integrity

Compare the on hand count stored in each inventory part record with the sum of the transactions that add to or delete from that count.

SELECT vInvtItem.nKey,
  vInvtItem.sItemType,
  sFullName,
  rOnHandCount,
  SUM(rItemCount) AS SumItemCount,
  rOnHandCount - SUM(rItemCount) AS Difference,
  COUNT(rItemCount) AS CountItemCount

FROM vInvtItem INNER JOIN aTranLine
  ON vInvtItem.nKey = aTranLine.kItem

WHERE bNoPost = False
  AND vInvtItem.sItemType
    IN('InvtPart', 'InvtAssy')

GROUP BY vInvtItem.nKey, vInvtItem.sItemType,
  sFullName, rOnHandCount

ORDER BY sFullName

Group: I - P52 - Jl Name: I520 Inventory - TranTypes That Deplete: analysis

List all types of transaction lines that deplete (remove) inventory from stock on hand.

Whenever rItemCount is negative, the inventory item is being removed from stock on hand. Whenever it is positive, stock on hand is being increased.

Resulting transaction types:

CashSale - item sold to customer

CustInvc - item sold to customer

VendCred - item returned to vendor for credit

InvAdjst - Adjustment entry was made that removed items to what was on hand

Estimates also fall into this category, but are eliminated with the 'bNoPost = False' clause.

REDO USING COUNT AGGREGATE - THEN RECHECK TYPE

SELECT dDate, sTranType, kItem, sFullName,
  vInvtItem.sItemType, rItemCount,
  aTranLine.rUnitPrice, mAmount,
  kAcct, sAcctType

FROM vInvtItem LEFT JOIN aTranLine
  ON vInvtItem.nKey = aTranLine.kItem

WHERE rItemCount <= 0
  AND bNoPost = False
  AND (vInvtItem.sItemType = 'InvtPart'
       or vInvtItem.sItemType = 'NonInvtPart')

ORDER BY sTranType, dDate

Group: I - P54-ds - J4 Name: I540 Sales Taxes from Cust to Vendor

Shows all collections of 'TaxItem's - with the customer they were collected from, the vendor they are owed to, etc.

It would be easy to add a pivot query to summarize these collections by sTranType, CustJob, Vendor, TaxName, etc. fpr specialized reporting.

SELECT dDate, sTranType,
  kAcct, sAcctType, -mAmount AS Amount,
  cCustJob.sFullName AS CustJobName,
  vVendor.sName AS VendorName,
  vInvtItem.sName AS TaxName, aTranLine.rUnitPrice,
  sMemo80

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

WHERE aTranLine.sItemType = 'TaxItem'
  AND bNoPost = False

ORDER BY dDate, sTranType, kDoc, nLine

Group: I - P55-m - J3 Name: I550 Fixed Asset - General

Show all transactions involving fixed assets - displaying several fixed asset related fields. List the output by the full name of the fixed asset.

SELECT kItem, vInvtItem.sFullName,
  aTranLine.sItemType, dDate, sTranType,
  rItemCount, aTranLine.rUnitPrice, mAmount,
  aAccount.sFullName, aAccount.sAcctType,
  dPurch, sVendor, sAssetDescr, rCostBasis

FROM
  (vInvtItem INNER JOIN aTranLine
    ON vInvtItem.nKey = aTranLine.kItem)
  INNER JOIN aAccount
    ON aTranLine.kAcct = aAccount.nKey

WHERE bNoPost = False
  AND (aTranLine.sItemType IN ("FixedAsset"))

ORDER BY vInvtItem.sFullName

Group: I* - P58-ds - JlGr Name: I580 Inventory - Item Sales: totals in date range

List of total sales of normal items that are sold to customers (services, inventory items, or non inventory items). Sales total is for all activity in specified year(s).

Note the use of IN to include years and inventory type items. In each case, multiple entries can be included in the parenthesis.

Inventory items can be any of 12 types:

"Service", "InvtPart", "InvtAssy", "NonInvtPart", "FixedAsset", "OthCharge", "SubTotal", "Group", "Discount", "Payment", "TaxItem", "TaxGroup"

SELECT vInvtItem.sItemType, kItem, sFullName,
  FORMAT(-SUM(rItemCount * aTranLine.rUnitPrice),
      "0.00")
    AS TotalSales

FROM vInvtItem LEFT JOIN aTranLine
ON vInvtItem.nKey = aTranLine.kItem

WHERE DATEPART("yyyy", dDate) IN (2013)
 AND vInvtItem.sItemType
    IN ("Service", "InvtPart", "NonInvtPart")

GROUP BY vInvtItem.sItemType, kItem, sFullName

Group: I - P72-dm - <70 more? Name: I720 Inventorys - Adjustments

Shows all inventory adjustments. These should each be either a count adjustment or a value adjustment.

SELECT
    aTranLine.sTranType,
    aTranLine.kDoc,
    aTranLine.nLine,

    vInvtItem.sItemType,
    aTranLine.kItem,
    vInvtItem.sFullName,
    aTranLine.rItemCount,
    aTranLine.rUnitPrice,
    aTranLine.bUnitPricePcnt,

    aTranLine.kAcct,
    aAccount.sFullName,
    aTranLine.sAcctType,
    aTranLine.mAmount
FROM
  (aAccount RIGHT JOIN aTranLine
    ON aAccount.nKey = aTranLine.kAcct)
  LEFT JOIN vInvtItem
    ON aTranLine.kItem = vInvtItem.nKey

WHERE aTranLine.sTranType = 'InvAdjst'    
    AND
  aTranLine.bNoPost = False

ORDER BY kDoc, nLine

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: I - P80-m - J3 Name: I800 Inventory - Invtitem Receiving TranTypes

Outputs all inventory transactions where inventory items are actually received (added to stock on hand). Note that purchase orders do not receive inventory items.

Transaction types that could occur:

CardChrg - the company credit card was used to purchase an inventory item

Check - Receipt of item was initially recorded when a check was written to pay for the item

CustCred - item returned by customer

InvAdjst - Adjustment entry was made that added items to what is on hand.

ItemRcpt - item has been received, but no bill has arrived from the vendor. This type of transaction is temporary and will be changed to VendBill as soon as the bill arrives.

VendBill - similar to ItemRcpt, but the bill from the vendor has been received. ItemRcpt transactions are converted to VendBill transactions when the bill is received.

SELECT sTranType, dDate,
  kItem, vInvtItem.sFullName,   vInvtItem.sItemType,
  rItemCount, aTranLine.rUnitPrice, mAmount,
  kAcct, aAccount.sFullName, aTranLine.sAcctType

FROM
  (aAccount INNER JOIN aTranLine
    ON aAccount.nKey = aTranLine.kAcct)
  INNER JOIN vInvtItem
    ON vInvtItem.nKey = aTranLine.kItem

WHERE bNoPost = False
  AND rItemCount >= 0
  AND ((vInvtItem.sItemType = 'InvtPart'
    OR vInvtItem.sItemType = 'NonInvtPart'))

# ORDER BY vInvtItem.sFullName, dDate
ORDER BY sTranType

Group: I - P81 - J3 Name: I810 Inventory - Assembly Items

Shows all transactions for a specific set of inventory parts or services - by type of inventory item.

The IN set above can include any of: Service, InvtPart, InvtAssy, NonInvtPart, FixedAsset, OthCharge, Subtotal, Group, Discount, Payment, TaxItem, TaxGrou

SELECT kItem, vInvtItem.sFullName,
  aTranLine.sItemType, dDate, sTranType,
  aTranLine.kDoc, aTranLine.nLine, rItemCount,
  aTranLine.rUnitPrice, mAmount, kAcct,
  aAccount.sFullName, aAccount.sAcctType

FROM
  (vInvtItem INNER JOIN aTranLine
    ON vInvtItem.nKey = aTranLine.kItem)
  INNER JOIN aAccount
    ON aTranLine.kAcct = aAccount.nKey

WHERE bNoPost = False
  AND (aTranLine.sItemType IN ("InvtAssy"))

ORDER BY vInvtItem.nKey, aTranLine.kDoc,
         aTranLine.nLine

Group: I - P82-s - PiJiGr Name: I820 Inventory Services - Hours: by time period

Sums count of hours for each service type item by time period. Each column contains data for a single time period (sorted). In this query, the time period is a quarter, but could be easily changed.

Each row contains the totals for a single service - sorted alphabetically from left to right.

As written, this query collects data for all time periods in the file. A single addition to the WHERE clause could confine it to a lesser range

TRANSFORM FORMAT(AVG(-rItemCount), "0.0")

SELECT vInvtItem.sFullName

FROM vInvtItem INNER JOIN aTranLine
  ON vInvtItem.nKey = aTranLine.kItem

WHERE vInvtItem.sItemType = 'Service'
  AND bNoPost = False

GROUP BY vInvtItem.sFullName

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

----------------------
  for monthly:
PIVOT YEAR(dDate) & '-' & FORMAT(dDate, 'mm')

Group: I - P84-ms<< - PiJiGr Name: I840 Inventory Services - Revenue: by time period

Sums revenue for each service type item by time period. Each column contains data for a single service - sorted alphabetically left to right.

Each row contains the totals for a single time period - a month in this case, but could be easily changed to week, quarter, year, etc.

Add to the WHERE clause to confine the date range.

TRANSFORM SUM(-mAmount)

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

FROM vInvtItem INNER JOIN aTranLine
  ON vInvtItem.nKey = aTranLine.kItem

WHERE vInvtItem.sItemType = 'Service'
  AND bNoPost = False

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

PIVOT vInvtItem.sFullName

----------------------
  to swap rows with columns, change these
  (note that GROUP BY needs to be same as SELECT)

SELECT vInvtItem.sFullName
GROUP BY vInvtItem.sFullName
PIVOT YEAR(dDate) & '-' & FORMAT(dDate, 'mm')

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