Managing Employee Expense policies often requires identifying who has the highest spending authority within your organization. With SuiteQL function, you can quickly rank Employees based on their assigned Expense Limits, making it easier to audit approvals, review policy compliance, or analyze expense management structures.
Suppose you want to see how Employees rank according to their Expense Limit. Instead of manually sorting records and assigning positions, you can use SuiteQL function to automatically generate rankings.
SuiteQL Example
SELECT
employee.entityid, -- Employee ID / Name
employee.expenselimit, -- Assigned expense limit
-- Rank employees from highest to lowest expense limit.
-- Employees with the same expense limit receive the same rank.
DENSE_RANK() OVER (
ORDER BY employee.expenselimit DESC
) AS expense_rank
FROM employee
-- Exclude employees without an expense limit
WHERE employee.expenselimit IS NOT NULL
-- Display results by rank, then employee ID
ORDER BY expense_rank, employee.entityid;
- This query ranks employees from the highest to the lowest expense limit. Employees with the same Expense Limit receive the same rank, and no ranking numbers are skipped because the DENSE_RANK() function is used.
To easily execute a SuiteQL query, see NetSuite Admin Tip | Running SUITEQL Using a Suitelet
DISCLAIMER: The sample code described herein is provided on an "as is" basis, without warranty of any kind, to the fullest extent permitted by law. Oracle + NetSuite Inc. does not warrant or guarantee the individual success developers may have in implementing the sample code on their development platforms or in using their own Web server configurations.
Oracle + NetSuite Inc. does not warrant, guarantee or make any representations regarding the use, results of use, accuracy, timeliness or completeness of any data or information relating to the sample code. Oracle + NetSuite Inc. disclaims all warranties, express or implied, and in particular, disclaims all warranties of merchantability, fitness for a particular purpose, and warranties related to the code, or any service or software related thereto.
Oracle + NetSuite Inc. shall not be liable for any direct, indirect or consequential damages or costs of any type arising out of any action taken by you or others related to the sample code.
We’d love to hear how you integrate SuiteQL into your NetSuite processes! Share your insights and experiences in the NetSuite Admin Corner.