Result set based on Range - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Result set based on Range

Author

Topic

udayfn12
Starting Member

15 Posts

Posted - 2002-08-06 : 14:11:42

Hi,

I wanted to get the result set based on Range of 5. Below is the entity and data. The range will be based on column percentile.

create table test (
docket varchar(5) unique,
ratio float,
percentile int)

insert into test values (1213, .11, 0)
insert into test values (2123, .12, 0)
insert into test values (1321, .14, 0)
insert into test values (1324, .18, 0)
insert into test values (1546, .40, 0)
insert into test values (1656, .49, 0)
insert into test values (1546, .99, 0)
insert into test values (21213, 1.11, 1)
insert into test values (22123, 1.12, 1)
insert into test values (21321, 3.14, 2)
insert into test values (21324, 3.18, 3)
insert into test values (31546, 4.40, 4)
insert into test values (41656, 2.49, 4)
insert into test values (51546, 2.99, 5)
insert into test values (51547, 3.99, 5)
insert into test values (32213, 11.11, 6)
insert into test values (23243, 11.56, 6)
insert into test values (32421, 13.14, 7)
insert into test values (43324, 43.18, 8)
insert into test values (56646, 54.40, 9)
insert into test values (53456, 52.49, 10)
insert into test values (45546, 52.99, 10)
insert into test values (67213, 44.00, 11)
insert into test values (89243, 48.44, 12)
insert into test values (87921, 65.45, 13)
insert into test values (76884, 65.78, 13)
insert into test values (98646, 74.23, 14)
insert into test values (99999, 75.00, 14)
insert into test values (76546, 88.34, 15)
insert into test values (45213, 144.00, 16)
insert into test values (88888, 148.44, 16)
insert into test values (34556, 165.45, 17)
insert into test values (45344, 165.78, 17)
insert into test values (34512, 174.23, 18)
insert into test values (54612, 175.00, 18)
insert into test values (34765, 188.34, 20)
insert into test values (54255, 175.00, 19)
insert into test values (32499, 188.34, 20)

I wanted result set something like this.

Result
------

Percentile Range Ratio Range #Dockets
<= 0 0.12 - 0.48 5
1- 5 1.11 - 3.99 8
6-10 11.10 - 52.99 7

Any help would be greatly appreciated.

Thanks,
Reddy.

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-08-06 : 14:47:16

Something like this outa do the trick...


select
    case
        when percentile <= 0 then '<=0'
        when percentile between 1 and 5 then '1-5'
        when percentile between 6 and 10 then '6-10'
        when percentile between 11 and 15 then '11-15'
        when percentile between 16 and 20 then '16-20'
    end as [Percentile Range],
    convert(varchar,min(ratio)) + ' - ' + convert(varchar,max(ratio)),
    count(*) as [#Dockets]
from
    test
group by 
    case
        when percentile <= 0 then '<=0'
        when percentile between 1 and 5 then '1-5'
        when percentile between 6 and 10 then '6-10'
        when percentile between 11 and 15 then '11-15'
        when percentile between 16 and 20 then '16-20'
    end
order by
    case
        when percentile <= 0 then '<=0'
        when percentile between 1 and 5 then '1-5'
        when percentile between 6 and 10 then '6-10'
        when percentile between 11 and 15 then '11-15'
        when percentile between 16 and 20 then '16-20'
    end

Jay White
{0}

r937
Posting Yak Master

112 Posts

Posted - 2002-08-06 : 14:53:23

if you don't mind hardcoding the percentile ranges, try

select '<= 0' as "Percentile Range"
     , cast(min(ratio) as varchar(10)) ||'-'||
       cast(max(ratio) as varchar(10)) as "Ratio Range"
     , count(*)
  from test
 where percentile <= 0
union all
select '1 - 5' 
     , cast(min(ratio) as varchar(10)) ||'-'||
       cast(max(ratio) as varchar(10)) 
     , count(*)
  from test
 where percentile between 1 and 5
union all
select '6 - 10' 
     , cast(min(ratio) as varchar(10)) ||'-'||
       cast(max(ratio) as varchar(10)) 
     , count(*)
  from test
 where percentile between 6 and 10

repeat as necessary to the last range between 95 and 100

if you want to make it more compact, use an integers table --

create table integers (i integer);
insert into integers (i) values (0);
insert into integers (i) values (1);
insert into integers (i) values (2);
insert into integers (i) values (3);
insert into integers (i) values (4);
insert into integers (i) values (5);
insert into integers (i) values (6);
insert into integers (i) values (7);
insert into integers (i) values (8);
insert into integers (i) values (9);

and then replace all but the first select (which selects "<= 0") with the following --

select cast( i * 5 - 4 as varchar(2)) ||'-'||
       cast( i * 4 as varchar(2)) 
     , cast(min(ratio) as varchar(10)) ||'-'||
       cast(max(ratio) as varchar(10)) 
     , count(*)
  from test, i
 where percentile between i * 5 - 4 and i * 5
group
    by i * 5

caution: untested

note: i used the standard double pipes syntax for concatenation (for some reason the plus sign did not appear when i previewed this post)

rudy
http://rudy.ca/

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-08-06 : 15:14:15

quote:

note: i used the standard double pipes syntax for concatenation (for some reason the plus sign did not appear when i previewed this post)

They don't appear in preview but do appear in the posts

[url]http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=16936[/url]

HTH
Jasper Smith

MuffinMan
Posting Yak Master

107 Posts

Posted - 2002-08-06 : 15:15:47

This will group the results in ranges of 5.


select [Percentile Range] = Case 
		when percentile <= 0 Then '<= 0' 
		else Right('0' + Convert(varchar(20), ((((percentile / 5) + 1)-1)*5)+1 ),2) + ' - ' + 
		     Right('0' + Convert(varchar(20), ((percentile / 5) + 1)*5),2)
		end,
	[Ratio Range] = convert(varchar(20), min(ratio)) + ' - ' + convert(varchar(20), max(ratio)),
	[#Dockets] = count(*)
from test
group by Case 
		when percentile <= 0 Then '<= 0' 
		else Right('0' + Convert(varchar(20), ((((percentile / 5) + 1)-1)*5)+1 ),2) + ' - ' + 
		     Right('0' + Convert(varchar(20), ((percentile / 5) + 1)*5),2)
		end
order by 1


/* here are the results */
Percentile Range Ratio Range                                 #Dockets    
---------------- ------------------------------------------- ----------- 
<= 0             0.11 - 0.49                                 6
01 - 05          1.11 - 4.4                                  6
06 - 10          2.99 - 54.4                                 7
11 - 15          44 - 75                                     8
16 - 20          88.34 - 175                                 8
21 - 25          188.34 - 188.34                             2

r937
Posting Yak Master

112 Posts

Posted - 2002-08-06 : 16:50:06

muffinman, your sql looks sleeker than mine

i did not test mine with the original data

did you?

your last line says the percentile range 21-25 has ranges 188.34-188.34 and 2 dockets, but i don't see any percentile higher than 20 in the original data

what's up with that?

rudy

udayfn12
Starting Member

15 Posts

Posted - 2002-08-06 : 20:02:34

Thanks to all, for the queries and suggestions.

-Reddy

Edited by - udayfn12 on 08/07/2002 00:25:44

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources