Published on December 2, 2020.

A new NetSuite client that I'm working with has been struggling with inventory in two ways: They have inventory that isn't selling, and they're having problems meeting the demand for several items that are selling well. They asked me to help them by creating SuiteQL queries that provide data needed to analyze their inventory and make better, more informed decisions with regards to purchasing.

One of those queries has been particularly helpful, and I've included it below. For active items, it returns various quantities (on hand, committed, available, reorder point, preferred stock level), the preferred vendor, the average resale price and purchase cost, quantities sold (in the last 30 days, 90 days, year, and lifetime), and more.

SELECT
	Item.ItemID,
	BUILTIN.DF( Item.Parent ) AS ParentItem,
	Item.Description,
	Item.QuantityOnHand,
	Item.QuantityCommitted,
	Item.QuantityAvailable,
	Item.QuantityOnOrder,
	Item.QuantityBackOrdered,
	Item.ReorderPoint,
	Item.PreferredStockLevel,
	Item.LeadTime,
	Vendor.CompanyName AS PreferredVendor,
	Item.Cost AS PurchasePrice,
	ROUND( Item.AverageCost, 2 ) AS AverageCost,
	ROUND( Item.LastPurchasePrice, 2 ) AS LastPurchasePrice,
	( 
		SELECT
			MIN( Transaction.TranDate )
		FROM
			Transaction
			INNER JOIN TransactionLine ON
				( TransactionLine.Transaction = Transaction.ID )
		WHERE
			( TransactionLine.Item = Item.ID )
			AND ( Transaction.Type = 'SalesOrd' )
	) AS FirstSold,
	( 
		SELECT
			MAX( Transaction.TranDate )
		FROM
			Transaction
			INNER JOIN TransactionLine ON
				( TransactionLine.Transaction = Transaction.ID )
		WHERE
			( TransactionLine.Item = Item.ID )
			AND ( Transaction.Type = 'SalesOrd' )
	) AS LastSold,	
	( 
		SELECT
			MIN( Transaction.TranDate )
		FROM
			Transaction
			INNER JOIN TransactionLine ON
				( TransactionLine.Transaction = Transaction.ID )
		WHERE
			( TransactionLine.Item = Item.ID )
			AND ( Transaction.Type = 'PurchOrd' )
	) AS FirstPurchased,
	( 
		SELECT
			MAX( Transaction.TranDate )
		FROM
			Transaction
			INNER JOIN TransactionLine ON
				( TransactionLine.Transaction = Transaction.ID )
		WHERE
			( TransactionLine.Item = Item.ID )
			AND ( Transaction.Type = 'PurchOrd' )
	) AS LastPurchased,		
	( 
		SELECT
			( SUM( TransactionLine.Quantity ) * -1 )
		FROM
			Transaction
			INNER JOIN TransactionLine ON
				( TransactionLine.Transaction = Transaction.ID )
		WHERE
			( TransactionLine.Item = Item.ID )
			AND ( Transaction.Type = 'SalesOrd' )
			AND ( ( TRUNC( SYSDATE ) - Transaction.TranDate ) <= 30 )
	) AS QtySold30Days,	
	( 
		SELECT
			( SUM( TransactionLine.Quantity ) * -1 )
		FROM
			Transaction
			INNER JOIN TransactionLine ON
				( TransactionLine.Transaction = Transaction.ID )
		WHERE
			( TransactionLine.Item = Item.ID )
			AND ( Transaction.Type = 'SalesOrd' )
			AND ( ( TRUNC( SYSDATE ) - Transaction.TranDate ) <= 90 )
	) AS QtySold90Days,
	( 
		SELECT
			( SUM( TransactionLine.Quantity ) * -1 )
		FROM
			Transaction
			INNER JOIN TransactionLine ON
				( TransactionLine.Transaction = Transaction.ID )
		WHERE
			( TransactionLine.Item = Item.ID )
			AND ( Transaction.Type = 'SalesOrd' )
			AND ( ( TRUNC( SYSDATE ) - Transaction.TranDate ) <= 365 )
	) AS QtySold1Year,
	( 
		SELECT
			( SUM( TransactionLine.Quantity ) * -1 )
		FROM
			Transaction
			INNER JOIN TransactionLine ON
				( TransactionLine.Transaction = Transaction.ID )
		WHERE
			( TransactionLine.Item = Item.ID )
			AND ( Transaction.Type = 'SalesOrd' )
	) AS QtySoldLifetime
FROM 
	Item 
	LEFT OUTER JOIN ItemVendor ON
		( ItemVendor.Item = Item.ID )
		AND ( ItemVendor.PreferredVendor = 'T' )
	LEFT OUTER JOIN Vendor ON
		( Vendor.ID = ItemVendor.Vendor )
WHERE 
	( Item. ItemType = 'InvtPart' )
	AND ( ( Item.MatrixType IS NULL ) OR ( Item.MatrixType = 'CHILD' ) )
	AND ( Item.IsInactive = 'F' )
	AND ( ( Item.QuantityOnHand > 0 ) OR  ( Item.QuantityOnOrder > 0 ) OR  ( Item.QuantityBackOrdered > 0 ) )

The query returns a large number of values, and yet it isn't particularly complicated. In the primary query, there are only three joins. However, there are also several correlated subqueries that get transaction-related values, including the dates that an item was first / last sold / purchased, quantities sold in the last 30, 90, and 365 days, the total quantity sold across the item's lifetime, etc.

Before running the query, please be aware that, depending on the number of items setup in your account, and the volume of transactions, the query might take awhile to run. In one of my client's accounts, which has over 3,000 active products and transactions going back to 2011, the query takes an average of 15 seconds to complete. Depending on your situation, you might want to paginate the query results.

Also, I've found that for some of my clients, and especially those with OneWorld accounts, there are a few columns that are referenced in the query that aren't supported. Therefore, depending on how your account is configured, you might need to modify the query.

My client runs the query as part of a scheduled process. The results are automatically exported to an Airtable database, where users analyze and export the data for use in other systems.

If you have any questions about the query, please feel free to contact me. Or join the new SuiteQL channel on the NetSuite Professionals Slack Community.