checking for range

hey all,
what's the best way to express this in a query?
for each employee
take the salary and determine which range the particular salary falls in.
for instance:
40k
falls between 35-40k so the category is 1
number of categories are 1-12
thanks,
rodcharSELECT Employee,
CASE
WHEN Salary BETWEEN 35000 AND 40000 THEN 1
WHEN Salary BETWEEN 40001 AND 45000 THEN 2
.
.
.
END AS "Category"
FROM [Your Table]
Or, you could have a table that stores that salary categories, and JOIN to i
t
SELECT e.Employee, c.Category
FROM [Your Table] e INNER JOIN [Salary Categories] c
ON e.Salary BETWEEN c.StartingSalary and c.EndingSalary
"rodchar" wrote:

> hey all,
> what's the best way to express this in a query?
> for each employee
> take the salary and determine which range the particular salary falls in.
> for instance:
> 40k
> falls between 35-40k so the category is 1
> number of categories are 1-12
> thanks,
> rodchar|||select case when salary < 40 and salary > 35 then 1
when salary >= 40 and salary < x then 2
when salary >= x and salary < y then 3
..
when salary > z then 12
end as 'category'|||Besides the CASE examples posted, consider a table of categories with
their ranges, and JOIN to it with the range test:
FROM Employees JOIN Ranges
ON Employees.salary >= Ranges.RangeMin
AND Employees.salary < Ranges.RangeMax
Roy
On Thu, 18 May 2006 14:35:01 -0700, rodchar
<rodchar@.discussions.microsoft.com> wrote:

>hey all,
>what's the best way to express this in a query?
>for each employee
>take the salary and determine which range the particular salary falls in.
>for instance:
>40k
>falls between 35-40k so the category is 1
>number of categories are 1-12
>thanks,
>rodchar|||thanks everyone for the help. i appreciate it a lot.
"rodchar" wrote:

> hey all,
> what's the best way to express this in a query?
> for each employee
> take the salary and determine which range the particular salary falls in.
> for instance:
> 40k
> falls between 35-40k so the category is 1
> number of categories are 1-12
> thanks,
> rodchar|||nice clean solution!! thanks.