Transactions within a certain category

• kuyaglenn

  SSC Rookie

  Points: 48

  More actions

  May 21, 2010 at 10:44 am

  #235382

  So i have this report that I need to display how much water was consumed & put it in it's appropriate category. The categories are as follows

  0 Gallons

  1 - 2000 Gallons

  2001 - 4000 Gallons

  4001 - 6000 Gallons

  etc.

  How do i do this in the report, without creating a big long query to break it out for me.

  So my query would be as an example, and then let the report put the consumption value into it's respective category.

  SELECT ACCTNUM,CONSUMPTION FROM MYTABLE

• malavika.ramanathan

  SSCrazy

  Points: 2790

  More actions

  May 24, 2010 at 2:56 am

  #1171423

  I am not very clear of what you want...

  but i understood as you want to make those different ranges as diff columns and then put the corresponding data in them.

  you can try using CASE

  select acctnum,

  (select case when consumption= 0 then 0 ) as Cat1,

  (select case when consumption between 1 and 2000 then consumption) as Cat2,

  (select case when consumption between 2001 and 4000 then consumption) as Cat3

  ,(select case when consumption >4000 then consumption ) as Cat4

  from TABLE

  Let me know if this is incorrect

• malavika.ramanathan

  SSCrazy

  Points: 2790

  More actions

  May 24, 2010 at 2:58 am

  #1171426

  Hey sorry,

  i didnt notice that 4001 to 6000 gallons range...

  you can repeat the same for that range too..

  but are you sure these are the only possible ranges? otherwise you will have to write so many case statements as much of the ranges...

  let me know if it helps.