Getting a value based on upper and lower bound columns in table

Transact-SQL
1

a user enters a value in a textbox say 0.83
in my table i have an upper bound and lower bound column
for instance (my table structure):

range lower upper ranking
(0,0.5) 0 0.5 great
(0.5,1.0) 0.5 1.0 good
(1.0,1.25) 1.0 1.25 bad
(1.25,1.5) 1.25 1.5 terrible
(1.5,250) >1.5 foreclosure

i want to see if the user input value 0.83 lies between 0.5 and 1.0 and return Good

I am not sure how to do that

I have tried
select *
from table
where lower > 0.83

brings back terrible and foreclosure

i am looking for

where
0.83 is between (lower and upper)

or

0.8 in (0.5,1.0) (i.e. range column)

hopefully this makes sense

2

First for foreclosure you should have lower = 1.5 and upper = 99999 or whatever is bigger than your anticipated top number.

Then the query would be: DECLARE @variable = 0.83 SELECT * FROM table WHERE @variable >= lower and @variable < upper;

1 Like
3

Perfect, have a wonderful week

4

That seems OK to me

select *
from table
where 0.83 between lower and upper

Does that work?

Only slight issue is that the UPPER of one row is the same as LOWER of the next, so if your value exactly matched Upper / Lower you will get TWO rows matched. Thus you could do

select *
from table
where     0.83 >= lower 
      and 0.83 <  upper

(or you could use great-than lower and greater-than-or-equal to upper if your limit conditions are the other way round)