J Lyne J Lyne - 5 months ago 7
SQL Question

How do I get multiple "THEN" results from a Case expression?

First, I am thinking that I cannot do what I originally set out to do, so if there are any suggestions that move away from a case expression, I am open to it.

As simple as I can make it, here is what I want it to look like.

SELECT *
FROM Table_CRR
WHERE (QTR in ('1st Quarter', '2nd Quarter'))


However, I want to supply the filter from a single parameter so that when I select:

"1st Quarter" it gives nothing,
"2nd Quarter" returns "1st Quarter",
"3rd Quarter" returns both "1st Quarter" and "2nd Quarter" and
"4th Quarter" returns "3rd Quarter", "2nd Quarter", and "1st Quarter".


This is what I have.

@QuarterSelect VarChar(15)

AS

SET @QuarterSelect = Case
WHEN @QuarterSelect = '4th Quarter' THEN '3rd Quarter'
WHEN @QuarterSelect = '4th Quarter' THEN '2nd Quarter'
WHEN @QuarterSelect = '4th Quarter' THEN '1st Quarter'
WHEN @QuarterSelect = '3rd Quarter' THEN '2nd Quarter'
WHEN @QuarterSelect = '3rd Quarter' THEN '1st Quarter'
WHEN @QuarterSelect = '2nd Quarter' THEN '1st Quarter'
WHEN @QuarterSelect = '1st Quarter' THEN ''
ELSE ''
END

SELECT *
FROM Table_CRR
WHERE (QTR in (@QuarterSelect))


It obviously terminates and only gives me '2nd Quarter'. I am sure there is a elegant way to do this, I am just having trouble figuring it out.

Any help or suggestions would be much appreciated!

Example of what I want to see: When I set the @QuarterSelect parameter to '3rd Quarter'

|Key |Qtr |
---------------------
|772384 |1st Quarter|
|407038 |1st Quarter|
|790033 |1st Quarter|
|188914 |1st Quarter|
|1607947|2nd Quarter|
|1683954|2nd Quarter|
|1607937|2nd Quarter|
|1948397|2nd Quarter|
...

Answer

I might be over simplifying, but if your strings are consistent, then why not just do a compare for less than.

SELECT *
   FROM Table_CRR
   WHERE 
      QTR < '1st Quarter'   -- returns nothing, unless you have something else   

SELECT *
   FROM Table_CRR
   WHERE 
      QTR < '2st Quarter'   -- returns 1st since 1 is less than 2 string compare

SELECT *
   FROM Table_CRR
   WHERE 
      QTR < '3rd Quarter'   -- returns 1st and 2nd since 1 & 2 are less than 3 string compare

SELECT *
   FROM Table_CRR
   WHERE 
      QTR < '4th Quarter'   -- returns 1st, 2nd and 3rd since 1, 2 & 3 are less than 4 string compare
Comments