Published on January 29, 2024.

One of my NetSuite clients - a wholesale distributor of electronic components - is in the process of analyzing customer Return Authorizations. To help with the analysis, they asked me to develop several SuiteQL queries.

One of the first queries that I developed for them is shown below. It returns a list of items that were returned the most frequently in the previous year.

SELECT TOP 10
	TransactionLine.Item,
	BUILTIN.DF( TransactionLine.Item ) AS ItemName,
	Item.Manufacturer,
	SUM( TransactionLine.Quantity ) AS TotalQuantityReturned
FROM
	Transaction
	INNER JOIN TransactionLine ON
		( TransactionLine.Transaction = Transaction.ID )
	INNER JOIN Item ON
		( Item.ID = TransactionLine.Item )
WHERE
	( Transaction.Type = 'RtnAuth' )
	AND ( Transaction.Void = 'F' )
	AND ( Transaction.Voided = 'F' )
	AND ( Transaction.TranDate BETWEEN BUILTIN.RELATIVE_RANGES( 'LY', 'START' ) AND BUILTIN.RELATIVE_RANGES( 'LY', 'END' )  ) 
GROUP BY
	Item,
	BUILTIN.DF( TransactionLine.Item ),
	Manufacturer
HAVING
	SUM( TransactionLine.Quantity ) > 0 
ORDER BY
	TotalQuantityReturned DESC,
	ItemName ASC

Return authorizations are stored in the Transaction table, as transaction type "RtnAuth." So that's where the query starts. I join to the TransactionLine table so that I can get information about the item that was returned, and the quantity returned. From there, I join to the Item table to get the item's manufacturer.

In the WHERE clause, I'm specifying the type of transaction to be returned, indicating that I want voided transactions to be excluded, and I'm using BUILTIN.RELATIVE_RANGES to specify that I only want returns that were issued in the previous year. (For more information about BUILTIN.RELATIVE_RANGES, see this blog post.)

I'm then grouping the results by Item (the internal ID of the item), the item's name (which I'm getting using BUILTIN.DF), and the manufacturer. By grouping the results, I can use the SUM function on the TransactionLine's Quantity column to get the total quantity returned.

The HAVING clause is used to filter the grouped results. In this case, I'm indicating that only the grouped rows whose total quantity is greater than 0 should be returned.

And finally, I'm sorting the results in descending order by the total quantity returned, and sub-sorting by the item name. This, combined with SELECT TOP 10 clause, returns only the top 10 most returned items.

This query was the foundation for several other returns-related queries that my client asked me to develop. For example, they also asked for a list of the customers who were issued the most return authorizations.

For additional SuiteQL queries involving Return Authorizations, see this post from December 2020.