locked
Case statement in the Where Clause? RRS feed

  • Question

  • Hi there, 

    This is what I'm trying to accomplish here; I have some orders that depends on the status, I need to query on different date fields.

    i.e. orders 1, 2, 3 status = 'completed' then the where  clause should limit in the date1 field

    order 4, 5, 6 status = 'in progress' then the where clause should limit in the date2 field

    and so on.

    Thanks,

    Manny

     

     

    Tuesday, October 11, 2011 4:24 PM

Answers

  • You can use the following

     

    select ...
    
    FROM ...
    
    WHERE @dStartDate <= CASE WHEN Status IN (1,2,3) then CompletedDate else [InProgressDate] END
    
    AND @dEndDate > CASE WHEN Status IN (1,2,3) then CompletedDate else [InProgressDate] END
    

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Proposed as answer by DVR Prasad Tuesday, October 11, 2011 4:38 PM
    • Marked as answer by midway11 Tuesday, October 11, 2011 5:01 PM
    Tuesday, October 11, 2011 4:34 PM

All replies

  • You can use the following

     

    select ...
    
    FROM ...
    
    WHERE @dStartDate <= CASE WHEN Status IN (1,2,3) then CompletedDate else [InProgressDate] END
    
    AND @dEndDate > CASE WHEN Status IN (1,2,3) then CompletedDate else [InProgressDate] END
    

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Proposed as answer by DVR Prasad Tuesday, October 11, 2011 4:38 PM
    • Marked as answer by midway11 Tuesday, October 11, 2011 5:01 PM
    Tuesday, October 11, 2011 4:34 PM
  • Awesome, thanks very much for your help!!!

     

    Tuesday, October 11, 2011 5:01 PM