Published on May 9, 2021.

One of my NetSuite clients - a manufacturer of consumer electronics - uses NetSuite's Support Management feature to track issues, questions, and suggestions that are submitted by their customers. A few months ago, they asked me to develop a custom Web portal for them, to be used by their remote, contracted customer service team.

Their customer service manager recently requested a few enhancements to the portal's dashboard. The request was essentially to add widgets that will help them get a quick overview of the open support cases. In this post, I'll share some of the SuiteQL queries that I created to add those dashboard widgets.

But first, to give you a sense of how the queries are being used, here's a screen shot of the portal's dashboard.

Open Support Cases - Details

Let's start with a query that returns details about open support cases. The cases are stored in a table named SupportCase.

SELECT
	SupportCase.ID,
	SupportCase.StartDate,
	SupportCase.CaseNumber,
	BUILTIN.DF( SupportCase.Status ) AS Status,
	Customer.EntityTitle AS Customer,
	SupportCase.Title AS Subject,
	SupportCase.Issue,
	BUILTIN.DF( SupportCase.Category ) AS Category,
	BUILTIN.DF( SupportCase.Assigned ) AS AssignedTo,
	BUILTIN.DF( SupportCase.Origin ) AS Origin,
	BUILTIN.DF( SupportCase.Priority ) AS Priority,
	SupportCase.TimeElapsed,
	SupportCase.TimeOpen,
	SupportCase.TimeToAssign	
FROM
	SupportCase
	INNER JOIN Entity AS Customer ON
		( Customer.ID = SupportCase.Company )
WHERE
	-- Not Closed or Solved.
	( SupportCase.Status NOT IN ( 5, 7 ) )
ORDER BY
	SupportCase.TimeOpen DESC

I'm joining from that SupportCase to the Entity table to get the name of the customer that submitted the case.

I'm filtering the cases based on status. Specifically, I'm filtering out closed cases (those with a status of 5) and solved cases (with a status of 7).

I'm returning three time-related columns: TimeElapsed, TimeOpen, and TimeToAssign. The values of these columns are integers that represent the number of hours involved.

Open Support Cases Summarized by Assignee

The customer service manager also wants to see open support cases summarized by the customer service rep that they are assigned to. Specifically, she wanted to see the number of open cases that have been assigned to each rep, and the average number days and hours that their cases have been open. Here's that query.

SELECT
	BUILTIN.DF( Assigned ) AS AssignedTo,
	COUNT(*) AS OpenCaseCount,
	TRUNC( AVG( SYSDATE - StartDate ) ) AS AvgDaysOpen,
	TRUNC( AVG( ( SYSDATE - StartDate) * 24 ) ) AS AvgHoursOpen
FROM
	SupportCase
WHERE
	-- Not Closed or Solved.
	( Status NOT IN ( 5, 7 ) )
GROUP BY
	BUILTIN.DF( Assigned )
ORDER BY
	AssignedTo

Open Support Cases Summarized by Status

Similarly, the customer service manager also wants to see open support cases summarized by status. Here's that query.

SELECT
	BUILTIN.DF( Status ) AS Status,
	COUNT(*) AS OpenCaseCount,
	TRUNC( AVG( SYSDATE - StartDate ) ) AS AvgDaysOpen,
	TRUNC( AVG( ( SYSDATE - StartDate) * 24 ) ) AS AvgHoursOpen
FROM
	SupportCase
WHERE
	-- Not Closed or Solved.
	( Status NOT IN ( 5, 7 ) )
GROUP BY
	BUILTIN.DF( Status )
ORDER BY
	Status

Closed Support Cases Summarized by Assignee

And finally, the customer service manager also wants to see closed support cases summarized by the customer service rep that they are assigned to, that were resolved within a specified date range.

SELECT
	BUILTIN.DF( Assigned ) AS AssignedTo,
	COUNT(*) AS ClosedCaseCount,
	ROUND( AVG( TimeToClose ), 0 ) AS AvgHoursToClose
FROM
	SupportCase
WHERE
	-- Closed or Solved.
	( Status IN ( 5, 7 ) )
	AND ( ( StartDate + ( ( 1/24 ) * TimeToClose ) ) BETWEEN TO_DATE( '2020-01-01', 'YYYY-MM-DD' ) AND TO_DATE( '2020-12-31', 'YYYY-MM-DD' ) )
GROUP BY
	BUILTIN.DF( Assigned )
ORDER BY
	AssignedTo

A quick note about this final query: The challenge with it is that the "Date Closed" value (column name "EndDate") that you can see in the NetSuite UI is not available via SuiteQL. However, using the "TimeToClose" value, which represents the number of hours that it took to close a case, we can calculate the Date Closed. To do so, I'm using this formula: ( StartDate + ( ( 1/24 ) * TimeToClose ) )

If you'd like to see a specific case's start date, time to close, and end date, you can use a query like this:

SELECT
	StartDate,
	TimeToClose,
	StartDate + ( ( 1/24 ) * TimeToClose ) AS EndDate	
FROM
	SupportCase
WHERE
	ID = 6364

Wrapping Up

There are a few additional SupportCase-related tables that we have access to via SuiteQL, including SupportCaseEscalateHist, SupportCaseEscalateToMap, SupportCaseIssueMap, SupportCaseMediaItemMap, and SupportCaseSolutionMap, as well as a few others. I'll cover those tables in a future post.

In the meantime, if you have any questions about the SuiteQL queries that I've shared in this post, please feel free to contact me.