CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Abdul Khaliq
Abdul Khaliq

Reputation: 2285

SQL Multiple conditions (AND, OR) in WHERE clause yield incorrect result

I just want to return a list from table where date difference more than 15 days are returned. It only returns where RequestStatus=1 not getting from where RequestStatus=2.

Here is my query: 

SELECT * 
FROM User 
WHERE RequestStatus = 1 
   OR RequestStatus = 3 
  AND (DATEDIFF(DAY, GETDATE(), TaskCompletionDate)) > 15

Upvotes: 0

Views: 737

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

I would suggest writing the query as:

select *
from User
Where RequestStatus in (1, 2, 3) and
      TaskCompletionDate > DATEADD(day, 15, getdate())

By moving TaskCompletionDate outside the date functions, you give SQL Server more opportunities to optimize the query (for instance, by potentially making use of an index, if available and appropriate).

Upvotes: 4

Deep
Deep

Reputation: 3202

try this :

Select * from User Where (RequestStatus=1 or RequestStatus=3) and (DATEDIFF(day, getdate(), TaskCompletionDate))> 15

Upvotes: 1

Rahul
Rahul

Reputation: 77866

Use a SQL IN clause to specify all legitimate values for RequestStatus column in your WHERE condition like

Select * 
from User 
Where RequestStatus in (1,2,3)
and (DATEDIFF(day, getdate(), TaskCompletionDate))> 15

Upvotes: 4

Related Questions