Don Trembly - 1 year ago 53

SQL Question

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 Source

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`

.