SQL Query Analyzer - Multiple criteria in Where Clause 2

[JcTon]

Not obtaining the correct result set using the following in the WHERE clause in a SQL Query Analyzer query;

Where client_id number between '124.5' and '130.5' or between '305' and '340.2' or between '409.1' and 415.25'

In other words, some of the records in the result set shouldn't be there because they contain a field that "violates" another criteria in the WHERE clause.

 

[kaht]

Was there a question hidden there, somewhere in your post?

Try posting the query that you're running and what exactly you want it to do. Then someone will be able to help you.

-kaht

Lisa, if you don't like your job you don't strike. You just go in every day and do it really half-assed. That's the American way. - Homer Simpson
[small]<P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <.</B> bites again.[/small]

 

[JcTon]

Is it possible to specify multiple criteria within the WHERE clause of a sql query that invoves multiple instances of a range of numbers?

For example,

Select Client_Name, Client_ID, Service_Date
Where Service_Date > '05/01/2006' AND '04/15/2007'
and Client_id number between '124.5' and '130.5' or between '305' and '340.2' or between '409.1' and 415.25'

Currently, some of the records in the result set shouldn't be there because they have a Service_Date that is before 5/1/2006 or after 4/15/2007.

 

[SQLDenis]

Yoda: "use the power of parentheses Luke!"

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[JcTon]

AND (client_id number between '124.5' and '130.5')
or (client_id number between '305' and '340.2')

does not work.

I still receive records that violate the first criteria.

I believe that the use of "or" is the problem. Therefore, how should the WHERE clause be rewritten so that I extract all records that satisfy the first criteria and have a client_id number between '124.5' and '130.5' or has a client_id number between '305' and '340.2', etc.?

 

[ca8msm]

You have incrrect syntax in your where clause due to:

1) The date clause should contain the name before the second date
2) The parenthesis are still not correct

Try this example:

DECLARE @TABLE1 TABLE 
	(id int IDENTITY(1,1), clientid decimal(10,1), servicedate datetime)

INSERT @TABLE1 VALUES (1.5, '20070101')
INSERT @TABLE1 VALUES (1.7, '20070201')
INSERT @TABLE1 VALUES (1.9, '20070301')
INSERT @TABLE1 VALUES (2.1, '20070401')

SELECT id, clientid, servicedate
FROM @TABLE1
WHERE servicedate BETWEEN '20070102' AND '20070402'
AND ((clientid between 1.5 and 1.7) or (clientid between 2.0 and 2.1))

-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]