CODEWITHSUNDEEP
sql-servercasewhere-clause
Wilest
Wilest

Reputation: 1860

T Sql Case use between in case when with where clause

Trying to do a WHERE BETWEEN with a CASE WHEN:

SET @Qtr1 = datepart(MONTH,@dt1)
select 
SUM(QtyInvoiced) as QtrCase
,CustName
,Town
,Rep
from SalesSumNew 
where
FinMonth between
case when @Qtr1 between '1' and '3' THEN '10' and '12'
,case when @Qtr1 between '4' and '6' THEN '1' and '3'
,case when @Qtr1 between '7' and '9' THEN '4' and '6'
,case when @Qtr1 between '10' and '12' THEN '7' and '9'
end
group by CustName, Town, Rep
order by Town

Results in error:

Msg 156, Level 15, State 1, Line 57
Incorrect syntax near the keyword 'and'.

Upvotes: 1

Views: 1925

Answers (1)

podiluska
podiluska

Reputation: 51494

You can't do that.

Try calculating the range using maths and mod : something like...

where FinMonth between 
((((2+@qtr1)/3)+2)*3)%12+1,
and
((((2+@qtr1)/3)+2)*3+2)%12+1

And if your FinMonth is really a string field, then '10','11','12' are all between '1' and '3'

Upvotes: 3

Related Questions