Problems with select

Question

Post reply

Problems with select

Berth

Say Hey Kid

Points: 691
More actions
September 21, 2010 at 3:45 am

#238566

Hello,
I need some help with select clause
Exampel table looks like this:
id from to procent
1 0.00 20.00 7.90
2 21.00 60.00 10.70
3 61.00 140.00 13.70
4 141.00 240.00 16.50
What I want is to select correct procent based on user input
So if the input parameter is 58 then I want to select 10.70 as procent because 58 is in the range of 21 to 60
Tried with 'WHERE to<= 58 AND from >= 58' but this doesnt give any results.

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

crazy4sql SSCoach Points: 19590 More actions · Answer 1

have you tried between operator....

where .... between 45 and 65 ..... or whatever you want to put in between

----------
Ashish

Berth Say Hey Kid Points: 691 More actions · Answer 2

Berth

Say Hey Kid

Points: 691

September 21, 2010 at 5:02 am

#1224677

Not quite sure how you mean?

crazy4sql SSCoach Points: 19590 More actions · Answer 3

where to between 45(or whatever you want to start compre from) and 65(till what you want to compare)

so just ex :-

if you looking between value in 45 to 65 then

where id between 45 and 65

----------
Ashish

Berth Say Hey Kid Points: 691 More actions · Answer 4

Berth

Say Hey Kid

Points: 691

September 21, 2010 at 5:08 am

#1224681

Ok but from and to values are unknown

crazy4sql SSCoach Points: 19590 More actions · Answer 5

Tried with 'WHERE to<= 58 AND from >= 58' but this doesnt give any results.

this value you can compre using between operator.

I might not be getting your queries correctly, can you explain it more with example of results you want to see

----------
Ashish

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 6

Try this: (This is what ashish was mentioning)

Declare @UserSelection INT

SET @UserSelection = 58

select * from

(

select 1 ,0.00 ,20.00, 7.90

union all select 2, 21.00, 60.00 ,10.70

union all select 3 ,61.00 ,140.00 ,13.70

union all select 4 ,141.00 ,240.00, 16.50

) Tab (id, [to], [from] , procent)

-- This is what ashish was meaning

WHERE

@UserSelection BETWEEN [to] and [from]

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

/*

Exampel table looks like this:

*/

DROP TABLE #Exampel

CREATE TABLE #Exampel ([id] INT, [from] NUMERIC(6,2), [to] NUMERIC(6,2), [procent] NUMERIC(6,2))

INSERT INTO #Exampel (id, [from], [to], procent)

SELECT 1, 0.00, 20.00, 7.90 UNION ALL

SELECT 2, 21.00, 60.00, 10.70 UNION ALL

SELECT 3, 61.00, 140.00, 13.70 UNION ALL

SELECT 4, 141.00, 240.00, 16.50

-- check sample table

SELECT * FROM #Exampel

/*

What I want is to select correct procent based on user input

So if the input parameter is 58 then I want to select 10.70 as procent because 58 is in the range of 21 to 60

Tried with 'WHERE to<= 58 AND from >= 58' but this doesnt give any results.

*/

DECLARE @inputparameter INT

SET @inputparameter = 58

SELECT *

FROM #Exampel

WHERE @inputparameter BETWEEN [from] AND [to]

-- This looks like schoolwork. Be sure to read and understand the meaning of KEYWORDS.

Edit: Oops, ColdCoffee beat me to it.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 8

Chris Morris-439714 (9/21/2010)
Edit: Oops, ColdCoffee beat me to it.

Ah , never CM.. i always enjoy reading through your innovative ideas of solving questions, makes me learn a lot.. i enjoy your queries vry much 🙂

Berth Say Hey Kid Points: 691 More actions · Answer 9

Ok now it makes perfect sense.

I was thinkning in the wrong direction 🙂

Thanks very much

crazy4sql SSCoach Points: 19590 More actions · Answer 10

yuppyyy....finally someone understand me....will show this proof to my wife....:-D 😀 😀 😀

----------
Ashish

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 11

learningforearning (9/21/2010)
yuppyyy....finally someone understand me....will show this proof to my wife....:-D 😀 😀 😀

LOL but do you understand her? 😛

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

crazy4sql SSCoach Points: 19590 More actions · Answer 12

most of the time there is deadlock in between of us and then my daughter give priority to mom SPID and kills mine .......

----------
Ashish

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 13

ColdCoffee (9/21/2010)
Chris Morris-439714 (9/21/2010)
Edit: Oops, ColdCoffee beat me to it.
Ah , never CM.. i always enjoy reading through your innovative ideas of solving questions, makes me learn a lot.. i enjoy your queries vry much 🙂

Gosh :blush: thanks ColdCoffee

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden