Dynamic case when

Question

     SELECT
     COUNT(id), AgeRange
     FROM
     (
     select
       id,
      case
        when age < 0 then 'less than 0'
        when age >= 0 and age <=30 then '0-30'
        when age >= 31 and age <=60 then '31-60'
        when age >= 61 and age <=90 then '61-90'
        when age >= 91 then '91+'
         when age = null then 'NO INFORMATION'
      else 'no catagory'
    end AS AgeRange
 from queue 
 where DATE between '01-Apr-2011' and '05-May-2011'
 ) T
GROUP BY
    AgeRange;

I want these ageRanges (0-30, 31-60, 61-90) to be dynamic. it means these values should come from a table(as these are set up by user). user can set as many values as they want for getting result. How can I do that?

Answer 1

Assuming you have a second table like:

ASSIST_TABLE
FromAge|ToAge|Text
0|30|'0-30'

The you could do something like this.

 SELECT
 COUNT(id),
 FROM
 (
 select
   id,
   ISNULL(select text from ASSIST_TABLE
     where Age between FromAge andToAge),'NO CATEGORY') AS AGERANGE
from queue 
where DATE between '01-Apr-2011' and '05-May-2011'
 ) T
GROUP BY
AgeRange;

If you want to stick to your case-statement, you would have to make the statement dynamic. Which means generate first the query, store it into a variable and execute it at last. That is more work!