Stuck in ELSE part of CASE used in where condition

Question

I'm writing a SQL query where I want to use CASE statement in WHERE Clause.

My logic goes like below:

• If MemberStatusId = 1, then Collaborator.Unavailability = @NullDate

• If MemberStatusId = 2, then Collaborator.Unavailability = @LastDate

• Else Collaborator.Unavailability > @NullDate AND then Collaborator.Unavailability < @LastDate.

First attempt:

{Collaborator}.[UnavailabilityEndDate] = 
    CASE 
        WHEN @MemberStatusId = 1 THEN @NullDate
        WHEN @MemberStatusId = 2 THEN @LastDate
        ELSE @NullDate AND @LastDate 
    END;

I don't know what to write in ELSE part to make check the UnavailabilityEndDate between NullDate and LastDate.

Second attempt - but this results in an error:

CASE 
    WHEN @MemberStatusId = 1 THEN {Collaborator}. [UnavailabilityEndDate] = @NullDate 
    WHEN @MemberStatusId = 2 THEN {Collaborator}.[UnavailabilityEndDate] = @LastDate 
    ELSE {Collaborator}. [UnavailabilityEndDate] > @NullDate AND [Collaborator].[UnavailabilityEndDate] < @LastDate 

Error:

NOTE: I tried to code sample or Ctrl+K but it didn't work for me to format or style the code in the comment box.

Answer 1

CASE in a WHERE clause makes little sense. With CASE you can check a condition in order to generate a value. Why generate that value in order to check it again in the WHERE clause? Just check the condition in the WHERE clause right away instead. In your case:

WHERE (MemberStatusId = 1 AND Collaborator.Unavailability = @NullDate)
   OR (MemberStatusId = 2 AND Collaborator.Unavailability = @LastDate)
   OR (MemberStatusId NOT IN (1,2) AND Collaborator.Unavailability > @NullDate
                                   AND Collaborator.Unavailability < @LastDate)