Published on February 16, 2023.

A NetSuite colleague reached out to me last night, concerned that their account might be missing transactions as a result of the outage that occurred this week. They wanted to know if there was a way to use SuiteQL to calculate the average number of transactions (sales orders specifically) that are placed during each hour of the day. Their plan was to look at the averages and compare them to the outage period.

Here's the query that I developed for them.

SELECT
	Hour,
	ROUND( AVG( TransCount ), 0 ) AS AverageTransactions
FROM
	(
		SELECT
			TO_CHAR ( Transaction.TranDate, 'YYYY-MM-DD') AS Date,
			EXTRACT( HOUR FROM SystemNote.Date ) AS Hour,
			COUNT(*) AS TransCount
		FROM
			Transaction
			INNER JOIN SystemNote ON
				( SystemNote.RecordID = Transaction.ID )
				AND ( SystemNote.RecordTypeID = -30 )
				AND ( SystemNote.Field = 'TRANDOC.NKEY' )
		WHERE
			( Transaction.TranDate BETWEEN TO_DATE( '2023-01-01', 'YYYY-MM-DD' ) AND TO_DATE( '2023-02-13', 'YYYY-MM-DD' ) )
			AND ( TRIM( TO_CHAR(Transaction.TranDate, 'Day') ) IN ( 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday' ) )
			AND ( Transaction.Type = 'SalesOrd' )
		GROUP BY
			TO_CHAR (Transaction.TranDate, 'YYYY-MM-DD'),
			EXTRACT( HOUR FROM SystemNote.Date )
	)
GROUP BY
	Hour
ORDER BY
	Hour

If you want to adjust the date range being analyzed, then change this:

( TranDate BETWEEN TO_DATE( '2023-01-01', 'YYYY-MM-DD' ) AND TO_DATE( '2023-02-13', 'YYYY-MM-DD' ) )

For example, you might want to go as far back as last year:

( TranDate BETWEEN TO_DATE( '2022-01-01', 'YYYY-MM-DD' ) AND TO_DATE( '2023-02-13', 'YYYY-MM-DD' ) )

Obviously, the bigger the date range, the longer it will take for the query to run.

My client only wanted to analyze transactions placed during weekdays. But you can analyze other days of the week by adjusting this:

( TRIM( TO_CHAR(Transaction.TranDate, 'Day') ) IN ( 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday' ) )

For example, if you're really only interested in orders placed on Mondays and Tuesdays, you'd change it to:

( TRIM( TO_CHAR(Transaction.TranDate, 'Day') ) IN ( 'Monday', 'Tuesday' ) )

Details About the Query

The reason that I'm joining to the SystemNote table is that I need to get a timestamp value that represents when a transaction record was created. Unfortunately, the Transaction table's TranDate column only returns the date that the order was placed. By joining to SystemNote table - and specifically to the SystemNote record that represents the creation of the transaction - I can use the SystemNote's Date column, which is a timestamp.

The join to the SystemNote is also worth discussing. The SystemNote.RecordID column represents the ID of the object that the SystemNote is related to - so in this case, that's the Transaction record's ID. The "SystemNote.RecordTypeID = -30" filter specifies that we're looking for system notes related to transactions. And the "Field = 'TRANDOC.NKEY'" filter specifies that we want the system note that represents when NetSuite assigned an internal ID to the transaction - which is when the transaction record was created.

You might be wondering why I'm using the TRIM function in this WHERE filter:

AND ( TRIM( TO_CHAR(Transaction.TranDate, 'Day') ) IN ( 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday' ) ) 

It's because when you use the TO_CHAR function with the "Day" option, Oracle returns a right-padded value that's 8 characters in length. I believe it does this so that the day names that are returned have the same length.

Wrapping Up

While my colleague is using the query to look at the average number of transactions, you could adjust the query to analyze other things as well - such as the average dollar amounts of the orders that are placed.

And finally, keep in mind that you could also use the query to see the impact of other, non-NetSuite outages as well. For example, if your ecommerce system is down, you could use the query to get a sense of the impact that the outage had.

I enjoy working on ad hoc, rush requests like this - especially when I know how helpful they can be. I hope you find some value in the query that I've shared.