WHERE CASE

Question

Post reply

WHERE CASE

TB-462197

SSC Eights!

Points: 990
More actions
March 18, 2008 at 12:36 pm

#70890

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?

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

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

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?

TB-462197 SSC Eights! Points: 990 More actions · Answer 2

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!!!

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

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?

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

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?

TB-462197 SSC Eights! Points: 990 More actions · Answer 5

[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))

)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

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.

😎

TB-462197 SSC Eights! Points: 990 More actions · Answer 7

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

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!

😎

mrpolecat SSCertifiable Points: 7014 More actions · Answer 9

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

Sergiy SSC Guru Points: 110208 More actions · Answer 10

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

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 11

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)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 12

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?