Monday, June 4, 2012

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:

3Peter2011-05-11 21:26:32.4230
4Jon2011-05-11 21:26:32.4231
5Harry2011-04-11 14:10:34.3870
6Nagesh2010-05-11 14:10:34.3871

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 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 2nd and 3rd point.
Now just think, what you will do in coding to solve above problem....??? most probably.... you may write something like this:
    //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:
 (IsActive= 1 AND DATEDIFF(DAY,JoinedDate,GETDATE()) <= 10)
 (IsActive= 0 AND DATEDIFF(DAY,JoinedDate,GETDATE()) <= 50)

No comments:

Post a Comment