SuiteQL in NetSuite enables the extraction and manipulation of data from your NetSuite account. It provides users with flexibility and efficiency when accessing data by allowing them to design complicated queries to retrieve specific information.
Here’s an example SuiteQL query for calculating total revenues per day.
SELECTTranDate,COUNT(*) as Count,SUM(ForeignTotal) as TotalFROMTransactionWHERE( Type = 'SalesOrd' )AND ( TranDate = TO_DATE('2023-12-22', 'YYYY-MM-DD') )GROUP BYTranDate
Explanation of the Query
SELECT Clause:
- TranDate: The date of the transaction.
- COUNT(*) AS Count: Counts the number of sales orders on the specified date.
- SUM(ForeignTotal) AS Total: Calculates the total amount of sales orders for that day.
FROM Clause:
- Specifies the Transaction table from which the data is retrieved.
WHERE Clause:
- Type = ‘SalesOrd’: Filters the records to include only those of type ‘SalesOrd’, which stands for sales orders.
- TranDate = TO_DATE(‘2023-12-22’, ‘YYYY-MM-DD’): Filters the records to include only those with the transaction date of December 22, 2023. Note: You can adjust this date to any day you wish to review sales for.
GROUP BY Clause
- TranDate: Groups the results by the transaction date, ensuring that the COUNT and SUM functions are applied to each date separately.
This query helps organisations analyse their daily sales performance. By running this query, users may get the number of sales orders and total amount for a given day, allowing them to evaluate daily sales operations and make educated decisions based on the results.