CODEWITHSUNDEEP
sqlsql-server
Thomson
Thomson

Reputation: 143

Find the Range from a Table

I have a table with the following values

id Level Threshold
1   1     5000
2   2     10000
3   3     15000

What i need to achieve is that when i pass 6000 , I need to get Level 1. 12000 Level 2 and 16000 Level 3?

6000 - Level 1
12000 - Level 2 
16000 - Level 3

Can someone let me know how this can be achieved?

Upvotes: 0

Views: 63

Answers (4)

Sami
Sami

Reputation: 8419

Fiddle Demo

create table temp (id int, level int, Threshold int);

insert into temp values (1,1, 5000);
insert into temp values (2,2, 10000);
insert into temp values (3,3, 15000);


select max(LEVEL) from temp where Threshold<= 8000; (8000 or any other value)

Upvotes: 0

Indra Prakash Tiwari
Indra Prakash Tiwari

Reputation: 1057

Try Below example, might be you want below one

create table #temp (id int, value int)

insert into #temp values (1, 6000)
insert into #temp values (2, 12000)
insert into #temp values (3, 15000)
insert into #temp values (4, 16000)


select * from #temp 

select id, ceiling(convert(float,value)/6000) as level, value   from #temp

Upvotes: 0

Anders
Anders

Reputation: 8577

How about an SQL question like this?

SELECT One.Level, One.Threshold
FROM
    TableName AS One,
    (SELECT MAX(Threshold) AS Maximum FROM TableName WHERE Threshold <= :value) AS Two
WHERE One.Threshold = Two.Maximum

Replace :value with 6000, 12000, 16000 or whatever value you are interested in. The inner query finds the maximum threshold that the value has reached. The outer query returns the level number for threshold.

Disclaimar: I have not tested this.

Upvotes: 0

YLG
YLG

Reputation: 885

What I understood from your question is that when user will give 6000 then it should check which value is less than 6000 so it's 5000 and it's level is 1 same as when 12000 so it has two output as 5000 (level1) and 10000 (level2) but you need maximum one so it is 10000 (Level2). So according to this understanding the query is :

 select max(LEVEL) from Table where Threshold< 6000;

Upvotes: 1

Related Questions