Case statement in the Where Clause?

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

 

 

Answer 1

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

Answer 2

Awesome, thanks very much for your help!!!