Need recommendation on using case statement in the WHERE clause

  •  

    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 2 months, 3 weeks ago by  Tamrak.
    • This topic was modified 2 months, 3 weeks ago by  Tamrak.
  • 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?

  • 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.

  • 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
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

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

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

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