Need recommendation on using case statement in the WHERE clause

Question

Need recommendation on using case statement in the WHERE clause

Tamrak

SSC Eights!

Points: 902
More actions
June 28, 2021 at 8:23 pm

#3901360
I plan to put a variable in the SQL statement. Let’s say it is called @test. It will be just a Varchar(5). The value will be either Yes or No.
I plan to use in the WHERE clause
The logic will be the following:
If @ Test = ‘Yes’, query will look up into the table for a specific field. Let’s say it is called, ‘OccurrenceDate’. The records should be pulled from the last full three months until present date.
OccurenceDate >= Dateadd(Month, Datediff(Month, 0, Getdate())-3, 0)
If @ Test = ‘No’, query will look up into the table and pull the records only for the last three full months.
OccurenceDate >= Dateadd(Month, Datediff(Month, 0, Getdate())-3, 0) and OccurrenceDate < DATEADD(month, DATEDIFF(month, 0, Getdate()), 0)
WHEN Case (@Test = ‘Yes’ then option 3.1 ELSE option 3.2 END)
Do you have any suggestions on optimizing the WHERE clause on this?
Thank you.
- This topic was modified 3 years, 4 months ago by Tamrak.
- This topic was modified 3 years, 4 months ago by Tamrak.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

ratbak Ten Centuries Points: 1358 More actions · Answer 1

You're often better off using a UNION ALL to separate the cases into statements with their own sargeable where clauses -- e.g.,

SELECT ...
FROM ...
WHERE @Test = 'Yes' AND OccurrenceDate >= DATEADD(MOnth,DATEDIFF(MONTH,0,GETDATE())-3,0) AND OccurenceDate < DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0)
UNION ALL
SELECT ...
FROM ...
WHERE @Test = 'No' AND OccurrenceDate >= DATEADD(MOnth,DATEDIFF(MONTH,0,GETDATE())-3,0)

If they are either/or, the optimizer can just eliminate/ignore the irrelevant union statement(s) based on the fact the where clause is not true

Is it "OccurenceDate"(one r) or "OccurrenceDate" (two r's)?

Is "WHEN Case (@Test = ‘Yes’ then option 3.1 ELSE option 3.2 END)" relevant to the question? I didn't see how it fit in.

It will be just a Varchar(5). The value will be either Yes or No.

Relatively trivial, but why use a varchar(5) for a parameter than can only be three characters?

Tamrak SSC Eights! Points: 902 More actions · Answer 2

Hello,

Thank you for your reply.

The OccurrenceDate is the same field. It is my typo.

Let me try working on this and will work with your suggestions.

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 3

Instead of trying to use a case expression - it would be much simpler to calculate the values to be used. For example:

Declare @option numeric(3,1) = 3.2     --Default for @test = 'No'
      , @endDate datetime = getdate()  --default for @test = 'No'
      , @startDate datetime = dateadd(month, datediff(month, 0, getdate()) - 3, 0);

     If @test = 'Yes'
  Begin
    Set @option = 3.1;
    Set @endDate = dateadd(month, datediff(month, 0, getdate()), 0);
    End

 Select ...
   From ...
  Where ...
    And Option = @option
    And OccurrenceDate >= @startDate
    And OccurrenceDate <  @endDate;

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs