Deriving a thousands or hundreds range from a number in T-SQL

Question

We have several thousand job numbers stored in a SQL Server database, and I'd like to be able to derive what 1000's or 100's range they fall into for purposes of grouping in a third party application, which is integrated with our jobs list.

How can I extract the 1000's group that each job number would belong to in a column in my query's result set?

Examples: I would like for my output to be:

JOB_NUMBER      JOB_GROUP
678             0-999
679             0-999
1517            1000-1999
2011            2000-2999
2150            2000-2999
...etc.

Answer 1

If job_number is an integer, you can use this:

select job_number, 
       convert(varchar(10), job_number / 1000 * 1000) 
     + '-' 
     + convert(varchar(10), job_number / 1000 * 1000 + 999) Range
  from whatever

Answer 2

You could use a CASE expression:

SELECT 
JOB_NUMBER,
JOB_GROUP = CASE 
      WHEN JOB_NUMBER < 1000 THEN '0-999'
      WHEN JOB_NUMBER < 2000 THEN '1000-1999'
      WHEN JOB_NUMBER < 3000 THEN '2000-2999'
    END