SQL Conditional Where

Many of the times, we may come across with the situation where we need to put conditions on the WHERE clause. For example, we have the table:

ID	Name	JoinedDate	IsActive
1	Naveen	NULL	0
2	Kiran	NULL	1
3	Peter	2011-05-11 21:26:32.423	0
4	Jon	2011-05-11 21:26:32.423	1
5	Harry	2011-04-11 14:10:34.387	0
6	Nagesh	2010-05-11 14:10:34.387	1
7	Mahesh	NULL	1

Problem: Select the records who's joined date is less than 10 - if it is active and joined date is less than 50 - if it is inactive. But the ID's of that record should lie between 2 and 6.

When we try to write a query to solve above problem, the place where we struck is on the where clause! .

Usually,  we need not to think much here! The trick to solve this type of query is quite simple!

If we re-frame the problem......it says

• Select the records between 2 and 6
• Who's joined date is less than 10, if it is active
• or who's joined date is less than 50, if it is inactive.

The question seems much simpler now! and we can easily write the query SELECT * FROM MyTable WHERE Number BETWEEN 2 AND 6 but still many of the people may struck to achieve 2^nd and 3rd point.

Now just think, what you will do in coding to solve above problem....??? most probably.... you may write something like this:

If(IsActive==1)
{
    //Joined Date is Less than 10. That in SQL:
    //DATEDIFF(DAY,JoinedDate,GETDATE()) <= 10
}
Else if(IsActive==0) //or simply Else
{
    //Joined Date is Less than 50. That in SQL:
    //DATEDIFF(DAY,JoinedDate,GETDATE()) <= 50
}

We need the same effect in SQL query.  We can achieve it easily by following these procedures:

• Place AND between the condition IsActive==1 and it's true part DATEDIFF(DAY,JoinedDate,GETDATE()) <= 10.
• Apply the same procedure to  Else if condition too i.e. place the AND between IsActive==0and DATEDIFF(DAY,JoinedDate,GETDATE()) <= 50 
• Now place OR between these statements.

So, the above code is logically equivalent to :

(IsActive= 1 AND DATEDIFF(DAY,JoinedDate,GETDATE()) <= 10) --if condition is ANDed with its true part.
OR --Two statements are combined with OR
(IsActive= 0 AND DATEDIFF(DAY,JoinedDate,GETDATE()) <= 50) --else if condition is ANDed with its true part.

Hence, our final query becomes:

SELECT * FROM MyTable
 WHERE Number BETWEEN 2 AND 6
  AND
 (IsActive= 1 AND DATEDIFF(DAY,JoinedDate,GETDATE()) <= 10)
  OR
 (IsActive= 0 AND DATEDIFF(DAY,JoinedDate,GETDATE()) <= 50)