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 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 it

SELECT e.Employee, c.Category
FROM [Your Table] e INNER JOIN [Salary Categories] c
ON e.Salary BETWEEN c.StartingSalary and c.EndingSalary

Show quoteHide quote

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
<rodc***@discussions.microsoft.com> wrote:

Show quoteHide quote

nice clean solution!! thanks.

thanks everyone for the help. i appreciate it a lot.

Show quoteHide quote

can't delete a row from primary table - is there an SP for this?
Ranking Rows
T-SQL
Convert(datetime, '07/11/48') Returns Year 2048!!
UPDLOCK blocking readers