CODEWITHSUNDEEP
sqlsql-serversql-server-2012
evanburen
evanburen

Reputation: 309

CASE Statement in WHERE Clause SQL Server

I'm having trouble sorting this out. I want to see the fiscal quarters for Date_Received. When the @ReviewPeriodQuarter = 1 then I want the Date_Received months 10,11,12. If @ReviewPeriodQuarter = 2 then I want the Date_Received months 1,2,3 etc. SQL Server doesn't like the BETWEEN part of this. Thanks

DECLARE @ReviewPeriodQuarter Int

SELECT * FROM Table
WHERE
        MONTH(Date_Received) =
        CASE 
            WHEN @ReviewPeriodQuarter = 1 THEN BETWEEN 10 AND 12 
            WHEN @ReviewPeriodQuarter = 2 THEN BETWEEN 1 AND 3 
            WHEN @ReviewPeriodQuarter = 3 THEN BETWEEN 4 AND 6 
            WHEN @ReviewPeriodQuarter = 4 THEN BETWEEN 7 AND 9 
        END

Upvotes: 0

Views: 56

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270493

How about using values()?

SELECT *
FROM Table t JOIN
     (VALUES (1, 10, 12),
             (2, 1, 3),
             (3, 4, 6),
             (4, 7, 9)
     ) v(ReviewPeriodQuarter, lo, hi)
     ON MONTH(t.Date_Received) BETWEEN v.lo AND v.hi AND
        v.ReviewPeriodQuarter = @ReviewPeriodQuarter;

Or even more simply:

WHERE DATEPART(quarter, DATEADD(MONTH, -3, t.Date_Received)) = @ReviewPeriodQuarter

In other words, you don't need conditional logic to specify each quarter. You can just use date arithmetic.

Upvotes: 0

Thom A
Thom A

Reputation: 95827

I would, pesonally, move the ranges to outside the SELECT entirely, and then just use a simple WHERE:

DECLARE @MonthStart int,
        @MonthEnd int;

SELECT @MonthStart = CASE @ReviewPeriodQuarter WHEN 1 THEN 10,
                                               WHEN 2 THEN 1
                                               WHEN 3 THEN 4
                                               WHEN 4 THEN 7
                     END,
       @MonthEnd = CASE @ReviewPeriodQuarter WHEN 1 THEN 12,
                                             WHEN 2 THEN 3
                                             WHEN 3 THEN 6
                                             WHEN 4 THEN 9
                   END;

SELECT *
FROM dbo.[Table]
WHERE MONTH(Date_Received) BETWEEN @MonthStart AND @MonthEnd;

Note that this still won't be SARGable though, due to the use of MONTH(Date_Received) in the WHERE. I must admit, needing rows from a table for specific months, regardless of year, is a little odd. If that is your true requirement you might be better off "investing" in a Calendar Table you can JOIN to, and then just having a WHERE on the calendar table's CalendarMonth column; which would be SARGable.

Upvotes: 1

d0little
d0little

Reputation: 476

You can either do it with more parameters like Larnu or you can use your original method but tweaked

DECLARE @ReviewPeriodQuarter INT

SELECT *
FROM Table
WHERE MONTH(Date_Received) BETWEEN 
CASE 
    WHEN @ReviewPeriodQuarter = 1 THEN 10
    WHEN @ReviewPeriodQuarter = 2 THEN 1
    WHEN @ReviewPeriodQuarter = 3 THEN 4
    WHEN @ReviewPeriodQuarter = 4 THEN 7
END 
AND 
CASE 
    WHEN @ReviewPeriodQuarter = 1 THEN 12
    WHEN @ReviewPeriodQuarter = 2 THEN 3
    WHEN @ReviewPeriodQuarter = 3 THEN 6
    WHEN @ReviewPeriodQuarter = 4 THEN 9
END

Upvotes: 0

Related Questions