0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for programmers

declare @QUEUE table
(
  id int identity,
  age int
)

insert into @QUEUE values
(-1),(0),(1),(null)

SELECT coalesce(t.idcount, 0),
       c.agerangetext
FROM   (SELECT 1, 'less than 0' UNION ALL
         SELECT 2, '0-30'        UNION ALL
         SELECT 3, '31-60'       UNION ALL
         SELECT 4, '61-90'       UNION ALL
         SELECT 5, '91+'         UNION ALL
         SELECT 6, 'NO INFORMATION'
       ) as c(agerange, agerangetext)
  LEFT OUTER JOIN (SELECT COUNT(id) AS idcount,
                          agerange
                   FROM   (SELECT id,
                                  CASE
                                    WHEN age < 0 THEN 1
                                    WHEN age >= 0 AND age <= 30 THEN 2
                                    WHEN age >= 31 AND age <= 60 THEN 3
                                    WHEN age >= 61 AND age <= 90 THEN 4
                                    WHEN age >= 91 THEN 5 WHEN age IS NULL THEN 6
                                  END AS agerange
                           FROM   @QUEUE
                           --WHERE  DATE BETWEEN '01-Apr-2011' AND '05-May-2011'
                          ) t
                   GROUP  BY agerange) AS t
    ON c.agerange = t.agerange
ORDER  BY c.agerange

fork query

created jan 25 12 Mikael Eriksson