WHERE CASE

  • I'm trying to find the limitations of WHERE...CASE (Can you use an 'IN' expression? Can you use 'OR'?)

    Not to oversimplify but...

    If you had a table where the first column is MonthID (aka int values 1-12)

    And you wanted to pull information by calendar year parameter @Quarter (aka int values 1-4)

    is there a way to use WHERE... CASE syntax?

    Don't laugh-- I tried...

    SELECT *

    FROM tblData

    WHERE MonthID IN CASE

    WHEN @Quarter = 1 THEN (1,2,3)

    WHEN @Quarter = 2 THEN (4,5,6)

    WHEN @Quarter = 3 THEN (7,8,9)

    ELSE (10,11,12) END

    and I tried a few variants of OR (with similar failure)

    Any ideas?

  • You're not off as far as you think. It's just that CASE is use to conditionally return a value, so not an IN statement.

    Try it this way:

    SELECT *

    FROM tblData

    WHERE CASE

    WHEN @Quarter = 1 and MonthID IN (1,2,3) then 1

    WHEN @Quarter = 2 and MonthID IN (4,5,6) then 1

    WHEN @Quarter = 3 and MonthID IN (7,8,9) then 1

    WHEN @Quarter = 4 and MonthID IN (10,11,12) then 1

    ELSE 0 END =1

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • Wow, I totally bow to you.

    I love 'CASE' statements-- this particular format of WHERE CASE is new to me.

    (I could never find good examples of it)

    I will probably pull this one out of my hat once a week bare minimum!

    Thanks again!!!

  • Happy to give you something new to look at...:)

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • Also - a method without the CASE would look like...:

    ...

    WHERE

    ((Monthid-1)/3 +1)=@quarter

    ...

    Of course - the first method would allow for more efficient index use (if monthid was indexed).

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • [and one more variation --cheating off your case syntax]:

    SELECT *

    FROM tblData

    WHERE (

    (@Quarter = 1 and MonthID IN (1,2,3))

    OR (@Quarter = 2 and MonthID IN (4,5,6))

    OR (@Quarter = 3 and MonthID IN (7,8,9))

    OR (@Quarter = 4 and MonthID IN (10,11,12))

    )

  • Or

    SELECT

    *

    FROM

    dbo.tblData

    WHERE

    MonthID between ((3 * @Quarter) - 2) and (3 * @Quarter)

    If MonthID is indexed, you still get the benefit of it.

    😎

  • "WHY WASN'T I USING THIS FORUM A YEAR AGO?!" 🙂

  • Just a guess, did you know it existed a year ago?

    It is awesome some of the ideas and help you can get from people here!

    😎

  • I don't know how well this plays with the indexes but I think it is easier to read

    where

    case

    when monthid in (1,2,3) then 1

    when monthid in (4,5,6) then 2

    when monthid in (7,8,9) then 3

    when monthid in (10,11,12) then 4

    else 0 end = @quarter


  • I would suggest relational database approach.

    Something like this:

    [Code]

    SELECT *

    FROM tblData D

    WHERE EXISTS (select 1 from dbo.Calendar C

    where C.Month = D.MonthID

    AND C.[Quarter] = @Quarter

    )

    [/Code]

    _____________
    Code for TallyGenerator

  • Lynn Pettis (3/18/2008)


    Or

    SELECT

    *

    FROM

    dbo.tblData

    WHERE

    MonthID between ((3 * @Quarter) - 2) and (3 * @Quarter)

    If MonthID is indexed, you still get the benefit of it.

    😎

    Nice...

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (3/18/2008)


    Lynn Pettis (3/18/2008)


    Or

    SELECT

    *

    FROM

    dbo.tblData

    WHERE

    MonthID between ((3 * @Quarter) - 2) and (3 * @Quarter)

    If MonthID is indexed, you still get the benefit of it.

    😎

    Nice...

    sure is....I knew there had to be one - just didn't give it the time to simmer enough 🙂

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Viewing 13 posts - 1 through 12 (of 12 total)

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