Archives of the TeradataForum

Message Posted: Fri, 22 Aug 2008 @ 16:00:06 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

<-- Anonymously Posted: Fri, 22 Aug 2008 16:58 -->

hi Phil

here's my idea:

instead of labeling each row, you can build categories. if you need the labels, then put them into a table and join it.

     sel substr(date,1,7) Yr_Month,
         Svc_Ownr_Cd,
         Bucket,
         case
           when cl_amt = 0 and curr_bal > cl_amt then -20
           when curr_bal <= cl_amt               then -10
           when 100 * (curr_bal - cl_amt) / cl_amt > 25 then 25
           else cast(((100 * (curr_bal - cl_amt) / cl_amt) / 5) as integer)*5      -- build categories
         end as OL_Pct,
     c.label
         count(1) Number
     from  Table t
     join category_labels c
     on t.PL_Pct = c.category
     where ...
     group by 1,2,3,4.5

     create table category_labels (category,   label)

     -20,    '(0)  CL=0 & Bal>CL'
     -10,    '(1)  not OL'
       0,    '(2)  <= 10%'
      10,    '(3)  <= 15%'
      ...
      25,    '(6)  > 25%'

hope that helps.

cheers

	<Prev	Next>		<<First	<Prev	Next>	Last>>