Salary Range Search - 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

SQL Server Development (2000)

Salary Range Search

Author

Topic

WildMan
Starting Member

22 Posts

Posted - 2002-04-15 : 12:30:04

Hello,

I am working on a salary search where the users can search by a range of salaries. I have the salary table that holds the salaries for each job as such.

JobID BeginSalary EndSalary
1 10,000 20,000
2 25,000 35,000
3 60,000 80,000

Now the users can put in a salary range that they would like to search for. So if a user put in a salary range from say 15,000 to 40,000 only JobID 2 shows up in the search results. The search terms I am currently using are:

WHERE BeginSalary >= @SalaryStart AND EndSalary <= @SalaryEnd

As you can see using this form to search the table will only return the JobID that is inbetween @SalaryStart and @SalaryEnd. I need the search to also return the JobID 1 because the search starts at 15,000 it is also part of the Salary range for JobID 1. I would like it to return the JobID 1 as well.

Please let me know if this makes sense, and if any one has any good ideas of how to approach this.

Thanks,

Joey

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-04-15 : 12:54:01

This should work:

SELECT * FROM Salaries
WHERE @SalaryStart BETWEEN BeginSalary AND EndSalary
OR @SalaryEnd BETWEEN BeginSalary AND EndSalary

WildMan
Starting Member

22 Posts

Posted - 2002-04-15 : 14:30:38

Thanks for the response but that doesn't seem to be working very well.

Here is the table:
JobID BeginSalary EndSalary
1 10,000 20,000
2 25,000 35,000
3 60,000 80,000

If I put in the salary range to search for to 5,000 to 100,000 it returns nothing. If I put in 10,000 to 100,000 then it will return JobID 1 but not 2 or 3. If I put in 10,000 and 80,000 then it will return JobID 1 and 3 but not 2. Is there a better way to get this to work.

Thanks,

Joey

skond
Yak Posting Veteran

55 Posts

Posted - 2002-04-15 : 14:47:35

create table salary
(
begin_salary money,
end_salary money
)

insert into salary
values (10000,20000)
insert into salary
values (25000,35000)
insert into salary
values (60000,80000)

create proc test
(
@begin_salary money = 10000,
@end_salary money = 35000

)
as
select * from salary
where begin_salary >= (select min(begin_salary) from salary where begin_salary >= @begin_salary) and
end_salary <= (select max(end_salary) from salary where end_salary <= @end_salary)

skond
Yak Posting Veteran

55 Posts

Posted - 2002-04-15 : 15:38:26

My coworker thought up this query. See if it works:

SELECT *
FROM salary
WHERE (BeginSalary BETWEEN @SalaryStart AND @SalaryEnd) OR
(EndSalary BETWEEN @SalaryStart AND @SalaryEnd)

WildMan
Starting Member

22 Posts

Posted - 2002-04-15 : 17:58:33

Thanks for the responses. The second one seems to be working fine.

Joey

WildMan
Starting Member

22 Posts

Posted - 2002-04-16 : 16:34:19

Hello again,

Well the one solution works well except for a few things.

WHERE (BeginSalary BETWEEN @SalaryStart AND @SalaryEnd) OR
(EndSalary BETWEEN @SalaryStart AND @SalaryEnd)

This being the data in the table:

JobID BeginSalary EndSalary
1 10,000 20,000
2 25,000 35,000
3 60,000 80,000

Now if I do a search for 30,000 to 30,000 then it returns nothing if I do a search for 26,000 to 34,000 it returns nothing. That is the only problem with the script is if you are searching in the middle of a salary range it will not pull it back. Other than that it works fine.

Does any one else have any more good ideas on how to conquer this problem?

Thanks,

Joey

skond
Yak Posting Veteran

55 Posts

Posted - 2002-04-16 : 16:59:19

Try this
declare @SalaryStart money
declare @SalaryEnd money

set @SalaryStart = 21000
set @SalaryEnd = 30000

SELECT *
FROM salary
WHERE (
(@SalaryStart BETWEEN Begin_Salary AND End_Salary) OR (@SalaryEnd BETWEEN
Begin_Salary AND End_Salary)
) OR (
@SalaryStart <= Begin_Salary AND @SalaryEnd >= End_Salary)

skond
Yak Posting Veteran

55 Posts

Posted - 2002-04-16 : 17:10:20

The last query was from my co-worker.

WildMan
Starting Member

22 Posts

Posted - 2002-04-16 : 17:11:17

Thanks once again!

I had just tried something simular to your sql and got it to work but yours is a bit shorter in code.

Thanks,

Joey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources