Marwan,

Here's a script to give you some ideas on one way to
do this kind of thing:

create table RangeChoices (
rangeSetID int not null,
intervalName varchar(20) null,
intervalStart decimal(10,3) not null,
includeStart int not null default 1,
primary key (rangeSetID, includeStart, intervalStart)
)

insert into RangeChoices values (1,null,0,1)
insert into RangeChoices values (1,null,10,0)
insert into RangeChoices values (1,null,25,0)
insert into RangeChoices values (1,null,500,0)
insert into RangeChoices values (2,'Low_1',0,1)
insert into RangeChoices values (2,'Mid_1',25,1)
insert into RangeChoices values (2,'Top_1',10000,0)
go

-- provide standard interval names if not provided
update RangeChoices set
intervalName =
case when includeStart = 0 then '> ' else '>= ' end +
cast(intervalStart as varchar(20))
where intervalName is null
go

-- test data
create table testVals (
f float not null
)
go

insert into testVals
select 0 union all select 1.5 union all select 7.6 union all
select 8.3 union all select 150.5 union all select 25
union all select 1000
go

-- identify which interval each datum is in, using
-- a specified set of intervals
create function valsIntervals (
@rangeSetID int
) returns table as return
select f, intervalName
from testVals, RangeChoices R1
where R1.intervalStart = (
select max(R2.intervalStart)
from RangeChoices R2
where (
R2.intervalStart < testVals.f
or (R2.intervalStart = testVals.f
and R2.includeStart = 1)
) and R2.rangeSetID = @rangeSetID
)
and R1.rangeSetID = @rangeSetID
go

select f, intervalName from valsIntervals(1) order by f
select f, intervalName from valsIntervals(2) order by f

select intervalName, count(f) as valCount
from valsIntervals(1)
group by intervalName
order by min(f)


select intervalName, count(f) as valCount
from valsIntervals(2)
group by intervalName
order by min(f)
go

drop table RangeChoices
drop table testVals
drop function valsIntervals

-- Steve Kass
-- Drew University
-- Ref: 4F8C88C3-C0D5-4085-B39C-9B68FF4D49AF

[quoted text, click to view]