Don Trembly Don Trembly - 1 month ago 5
SQL Question

SQL: How to Consolidate CASE

I have to repeat a CASE statement twice, and I'm wondering if there's a way to consolidate it.

Basically, I have a:

where open_time >= '11/1/16' and open_time < '12/1/16'


The problem is I need to DATEADD to the open_time based on a CASE Statement. I don't know how to consolidate it together, so I'm doing it twice.
Like:

where
DATEADD(hh,
CASE WHEN MONTH(open_time)=1 THEN -5
ELSE -4
END
,open_time) >= '11/1/16'AND
DATEADD(hh,
CASE WHEN MONTH(open_time)=1 THEN -5
ELSE -4
END
,open_time) < '12/1/16'


Is there any way to make it so I don't have to repeat the entire CASE Statment?

Answer

Use a BETWEEN?

WHERE case ... end BETWEEN '11/1/16' AND '12/1/16'

Note that between is "inclusive", so a between b and c is a <= b AND b <= c.