Width_Bucket

Width_Bucket can be used to assign bucket value for a given expression that falls in a range of values. The WIDTH_BUCKET(ROUND(q_date.as_of-q_t.due_date,0), 1, 180, 6) specifies that the value between 1 and 180 is split into 6 buckets/segments (180/6 =30). The bucket values will be 1 for range between 1-30, 2 for range between 31-60, and so on. The value of 0 and value higher than 180 are out of bound values. As shown below 0 days open will have 0 as the bucket value and 235 days open will have a value of 7.

The same can be also done using CASE or DECODE(SIGN(..)).

AR Aging Computation With Width_Bucket

SELECT q_t.idx, TO_CHAR(q_t.due_date,'mm/dd/yyyy') due_date,
 ROUND(q_date.as_of-q_t.due_date,0) days_open,
 WIDTH_BUCKET(ROUND(q_date.as_of-q_t.due_date,0), 1, 180, 6) 
      AR_aging_bucket
FROM
 (SELECT TO_DATE('01-MAR-2012') as_of FROM DUAL)    q_date,
 (SELECT 1 idx, TO_DATE('02-DEC-2011') due_date FROM DUAL UNION
  SELECT 2 idx, TO_DATE('21-FEB-2012') due_date FROM DUAL UNION
  SELECT 3 idx, TO_DATE('10-JUL-2011') due_date FROM DUAL UNION
  SELECT 4 idx, TO_DATE('17-NOV-2011') due_date FROM DUAL UNION
  SELECT 5 idx, TO_DATE('11-JAN-2012') due_date FROM DUAL UNION
  SELECT 6 idx, TO_DATE('01-NOV-2011') due_date FROM DUAL UNION
  SELECT 7 idx, TO_DATE('01-OCT-2011') due_date FROM DUAL UNION
  SELECT 8 idx, TO_DATE('01-MAR-2012') due_date FROM DUAL
 ) q_t;

Width_Bucket Comparison with NTILE()

Oracle registered trademark of Oracle Corporation.

Last Revised on: February 23, 2012

73757