Simple AND OR - 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

General SQL Server Forums

New to SQL Server Programming

Simple AND OR

Author

Topic

staticbob
Yak Posting Veteran

99 Posts

Posted - 2006-04-05 : 10:18:03

Hi,

I need to return all records where ..
Contract = @Contract

AND

CrossReference is null or ""

I have this but I dont think its right..

Bob

where
a.Contract = @Contract AND a.crossreference is null OR a.crossreference = ""

"I dislilke 7am. If &am were a person, I would kick 7am in the biscuits." - Paul Ryan, dailyramblings.com

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-04-05 : 10:19:56

think about logical operator priority.
first expressions using AND is evaluated and then the OR.
so:
A and B or C -> (Result of A AND B) or C

what you need is
A and (B or C)

Go with the flow & have fun! Else fight the flow

Blog thingie: [URL="http://weblogs.sqlteam.com/mladenp"]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-05 : 10:24:18

or

a.Contract = @Contract AND Isnull(a.crossreference,'')=''

Madhivanan

Failing to plan is Planning to fail

staticbob
Yak Posting Veteran

99 Posts

Posted - 2006-04-05 : 10:36:14

Thanks guys,

What about this one.... I also want to say AND RequiredDate is < today(), i.e. its late.

where
i.Contract = @Contract AND ConstructionReceivedDate is null

"I dislilke 7am. If &am were a person, I would kick 7am in the biscuits." - Paul Ryan, dailyramblings.com

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-05 : 10:38:38

where
i.Contract = @Contract AND ConstructionReceivedDate is null
and RequiredDate <DateAdd(day,Datediff(day,getdate(),0),0)

Madhivanan

Failing to plan is Planning to fail

staticbob
Yak Posting Veteran

99 Posts

Posted - 2006-04-05 : 10:56:12

Select ssl.id, ssl.orderdate

from SubcontractorScheduleLines ssl
join SubcontractorSchedule s on s.id = ssl.SubcontractorSchedule

where
s.Contract = '23F9008'
and ssl.OrderDate >DateAdd(day,Datediff(day,getdate(),0),0)
AND OrderDone = 0

This returns a row where OrderDate = 10/10/2006 ?

I only want to see rows where the order date has passed and orderdone is 0.

Thanks again
Bob

"I dislilke 7am. If &am were a person, I would kick 7am in the biscuits." - Paul Ryan, dailyramblings.com

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-05 : 11:05:42

Test it and see if you get required result

Madhivanan

Failing to plan is Planning to fail

staticbob
Yak Posting Veteran

99 Posts

Posted - 2006-04-05 : 11:09:00

I have, I get 3 rows. 2 of the dates are in 2005 which is correct, but the 3rd row has an OrderDate of '2006-10-10 00:00:00.000', which is obviously not overdue, yet.

Bob

16 2005-09-12 00:00:00.000
19 2005-10-31 00:00:00.000
21 2006-10-10 00:00:00.000

"I dislilke 7am. If &am were a person, I would kick 7am in the biscuits." - Paul Ryan, dailyramblings.com

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-06 : 03:34:02

Post some sample data and the result you want

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources