Case expression in Where Clause TSQL

Question

I'm trying to write a case expression in a where clause but I'm having trouble getting the syntax right for what I want to do. In the where clause I want to have the case statement check the column 'IndividualRateOption' and based on that column I want to add to the where clause.

--- Get generic rates
            select  case when RateType = 'PRE' then 1
                         when RateType = 'RID' then 2
                         when RateType = 'QUL' then 3 end,
                    BillingEntityId,
                    @GroupID,
                    RateID,
                    TierItemId,
                    RateUnitType,
                    RateUnitValue,
                    RateType,
                    RateTypeEntityID,
                    EffectiveDate,
                    ExpirationDate,
                    Usage,
                    AgeFrom,
                    AgeTo,
                    Gender,
                    PayBrokerCommissions
              from #BillingEntityRates
             where case IndividualRateSelectionOption when 'ANV' then @AnniversaryDate between EffectiveDate and ExpirationDate
                    OR isnull(@AnniversaryDate,@MemberEligibilityDate) between EffectiveDate and ExpirationDate
                    and EntityId is null

if IndividualRateSelectionOption is 'ANV' I want to filter based on "@anniversaryDate between Effective and ExpirationDate and EntityId is null"

If IndividualRateSelectionOption is not 'ANV' I want to filter based off "isnull(@AnniversaryDate,@MemberEligibilityDate) between EffectiveDate and ExpirationDate and EntityId is null"

Above is what I tried so far but the it's complaining about syntax. Thanks in Advance.

You don't really need a case statement for this, I don't think. Just WHERE (x = 'ANV' AND date between x and y and id IS NULL) OR (x != 'ANV' AND date between x and y and id IS NULL) — pmbAustin, Jan 03 '20 at 19:37
While it may not be helpful for the current query, [this](https://stackoverflow.com/questions/10256848/can-i-use-case-statement-in-a-join-condition/10260297#10260297) answer shows how to use a `case` expression in an `on` clause. A `where` clause works the same way. — HABO, Jan 04 '20 at 04:30

Gordon Linoff · Accepted Answer · 2020-01-03T19:43:02.903

Don't use a case when simpler boolean logic works:

where (IndividualRateSelectionOption = 'ANV' and 
       @anniversaryDate between Effective and ExpirationDate and
       EntityId is null
      ) or
      (IndividualRateSelectionOption <> 'ANV' and 
       coalesce(@AnniversaryDate, @MemberEligibilityDate) between EffectiveDate and ExpirationDate and
       EntityId is null
      )

You can filter out the EntityId is null logic:

where EntityId is null and
      ( (IndividualRateSelectionOption = 'ANV' and 
         @anniversaryDate between Effective and ExpirationDate          
        ) or
        (IndividualRateSelectionOption <> 'ANV' and 
         coalesce(@AnniversaryDate, @MemberEligibilityDate) between EffectiveDate and ExpirationDate and
        )
      )

Case expression in Where Clause TSQL

1 Answers1