how to save and search on a range

Question

1.00/5 (1 vote)

See more:

I have a dropdown with salary range such as

INR. 10000 to 15000
INR. 15001 to 20000

1)how to search on a range by a range?

ex: Let I am searching 12000-20000 range
Now I have two ranges in database 10000-15000 and 15001-20000.
So I should get the two ranges as a result of my query.

My question is,is there any single sql query to do this type of select statement?

Posted 10-Nov-14 3:03am

souvikcode

Add a Solution

Comments

DamithSL 10-Nov-14 9:13am

if you store range as string in single column, first thing you need to do is change add two columns with relevant number type and insert range minimum value in one column and maximum value in another column.

souvikcode 18-Dec-14 4:29am

I already have done in that way,means I have two cols for max and min.Just need the query if any single query exists.

Sinisa Hajnal 10-Nov-14 9:30am

After you do that do:
SELECTwhatever, columns, you, need FROM YOUR_TABLE WHERE
minValue BETWEEN lowerRange and upperRange OR maxValue between lowerRange and upperRange OR (lowerRange BETWEEN minValue AND maxValue AND upperRange between minValue and maxValue;

Where min/max Value are your table columns and lower and upper range are values of your search. That is, you're asking for those that intersect the range or those that contain the range in its entirety.

Tomas Takac 10-Nov-14 10:02am

How does the table storing ranges look like?

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2014-11-10T04:10:00

Yes, you can, but you need to create stored procedure[^].

First option option - by passing 2 parameters:

SQL

CREATE PROCEDURE GetSalaryFromRange()
    @salFrom DECIMAL(8,0),
    @salTo DECIMAL(8,0)
AS
BEGIN 
    SELECT EmployeeName, Salary
    From TableName
    WHERE Salary BETWEEN @salFrom AND @salTo
END

Second option - by passing 1 parameter:

SQL

CREATE PROCEDURE GetSalaryFromStringRange()
    @stringRange VARCHAR(30)
AS
BEGIN 
    DECLARE @salFrom DECIMAL(8,0) = CONVERT(DECIMAL(8,0), LEFT(@stringRange, CHARINDEX('-', @stringRange) -1))
    DECLARE @salTo DECIMAL(8,0) = CONVERT(DECIMAL(8,0), RIGHT(@stringRange, CHARINDEX('-', @stringRange) +1), LEN(@stringRange) - CHARINDEX('-', @stringRange))

    SELECT EmployeeName, Salary
    From TableName
    WHERE Salary BETWEEN @salFrom AND @salTo
END

How to call it from code?
Walkthrough: Using Only Stored Procedures (C#)[^]
How to: Execute a Stored Procedure that Returns Rows[^]

Tomas Takac · Answer 2 · 2014-11-10T04:37:00

Assuming you have two parameters for upper and lower bound of the range you are searching and your table is named SalaryRange with two columns LowerBound & UpperBound. This code is for SQL Server.

SQL

SELECT * FROM SalaryRange sr
WHERE sr.LowerBound <= @UpperBound 
  AND sr.UpperBound >= @LowerBound

how to save and search on a range

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0