When reviewing customer credit policies, a finance team may need to understand how credit limits are distributed across its customer base. For example, they may want to know how many active customers have limits below 2,000 or at least 10,000. NetSuite administrators can use SuiteQL to summarize this information and support credit policy reviews.
Sample Query
This query divides credit limits from 0 to less than 10,000 into five equal ranges of 2,000.
SELECT
-- Divide credit limits into five equal ranges of 2,000.
WIDTH_BUCKET(creditlimit, 0, 10000, 5) AS credit_bucket,
-- Count customers in each bucket.
COUNT(*) AS customer_count,
-- Show the lowest and highest actual credit limits in each bucket.
MIN(creditlimit) AS lowest_credit_limit,
MAX(creditlimit) AS highest_credit_limit
FROM
customer
WHERE
-- Include only active customers with a credit limit.
isinactive = 'F'
AND creditlimit IS NOT NULL
GROUP BY
-- Group customers by their assigned bucket.
WIDTH_BUCKET(creditlimit, 0, 10000, 5)
ORDER BY
credit_bucket
Understanding the Results
Bucket | Credit Limit Range |
|---|
0 | Below 0 |
1 | 0 to less than 2,000 |
2 | 2,000 to less than 4,000 |
3 | 4,000 to less than 6,000 |
4 | 6,000 to less than 8,000 |
5 | 8,000 to less than 10,000 |
6 | 10,000 or higher |
Each regular bucket includes its lower boundary and excludes its upper boundary. For example, a credit limit of 4,000 belongs to bucket 3, while 6,000 belongs to bucket 4. Values of 10,000 or higher belong to the overflow bucket, 6.
The customer_count column shows how many matching customers belong to each bucket, while lowest_credit_limit and highest_credit_limit show their actual minimum and maximum credit limits rather than the bucket boundaries. Only buckets containing matching customers appear in the results. Adjust the lower limit, upper limit, and number of buckets to match your review requirements.
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.