t-sql: return range in which a given number falls

I am trying to figure out a good way to return a string 'name' of a range in which a given number falls. Ranges are spans of 1000, so the first range is '0000-0999', the second is '1000-1999' etc. For example, given the number 1234, I want to return the literal string '1000-1999'.

It seems to me that I could maintain a reference table with these ranges, like this

--create & populate temp table with ranges
create table #ranges (st int,en int)
go
insert into #ranges values(0,999)
insert into #ranges values(1000,1999)
insert into #ranges values(2000,2999)
go

--example query
select replace开发者_如何转开发(str(st,4),' ','0') + '-' + replace(str(en,4),' ','0') as TheStringIWant
from #ranges 
where 1234 between st and en

...but it seems to me that the ranges should be able to be determined from the given number itself, and that I shouldn't need the (redundant) reference table (or, for that matter, a function) just for this.

It also seems to me that I should be able to figure this out with just a bit of brain power, except that I've just had 2 beers in quick succession this evening...

---

Another way;

select case when value / 1000 < 1 
    then '0000-0999' 
    else cast(value / 1000 * 1000 as varchar(16)) + '-' + cast(value / 1000 * 1000 + 999 as varchar(16)) 
end

---

You can use mathematical functions to avoid using the temp table:

SELECT 1234, 
RIGHT('0000' + CAST(FLOOR(1234/1000.0) * 1000 AS VARCHAR(11)),4)
+ '-'
+ RIGHT('0000' + CAST( (FLOOR(1234/1000.0) * 1000) + 999 AS VARCHAR(11)),4)

---

In the shell, I can use integer-arithmetic to cut off the 234, and calculate the string with a simple formular, however it wouldn't produce 0000-0999 but 0-999 for the first 1000.

v=1234
echo $(((v/1000)*1000))-$(((v/1000)*1000+999))

1000-1999

I don't know how to adapt it to tsql - whether possible at all.

---

declare @range int;
set @range = 1000;
select replace(str(st,4),' ','0') + '-' +
       replace(str(st + @range,4),' ','0') as TheStringIWant
from (
  select st = v / @range * @range
  from (select v = 1234) s
) s