Case Statement in WHERE Clause

[geminihk]

Hi Gurus,

When I execute the following statements, I got the error as:

-------------------------------------------------
Server: Msg 170, Level 15, State 1, Line 16
Line 16: Incorrect syntax near '>'.
Server: Msg 170, Level 15, State 1, Line 24
Line 24: Incorrect syntax near '>'.

--------------------------------------------------
Declare @bmth int,@byear int
Set @bmth=1
Set @byear=2004

create table #tq1_csm_rank(q1_rank int IDENTITY(1,1),q1_revenue decimal(10,2),q1_firstname nvarchar(25),q1_lastname nvarchar(15),q1_cons nvarchar(3))

If @bmth>=1 and @bmth<>12
Begin
Insert into #tq1_csm_rank(q1_revenue,q1_firstname,q1_lastname,q1_cons)
select sum(q1_revenue) q1_revenue,firstname,lastname,cons
from
(select bdate,dnote,cons,firstname=Case When CHARINDEX(',',namefirst)<>0 Then Left(namefirst,CHARINDEX(',',namefirst)-1) Else ltrim(rtrim(namefirst)) end, ltrim(rtrim(namelast)) lastname,
q1_revenue=cons_rev*10
from xtbl_gplbill join tbl_cmusersdata on xtbl_gplbill.cons=tbl_cmusersdata.uuid
where (month(bdate)=12 and year(bdate)=@byear-1) or
Case When @bmth<=2 Then (month(bdate)>=1 and month(bdate)<=2 and year(bdate)=@byear)
Else (month(bdate)<=@bmth and year(bdate)=@byear)
End
union all
select bdate,dnote,csm,firstname=Case When CHARINDEX(',',namefirst)<>0 Then Left(namefirst,CHARINDEX(',',namefirst)-1) Else ltrim(rtrim(namefirst)) end, ltrim(rtrim(namelast)) lastname,
q1_revenue=csm_rev*10
from xtbl_gplbill join tbl_cmusersdata on xtbl_gplbill.csm=tbl_cmusersdata.uuid
where (month(bdate)=12 and year(bdate)=@byear-1) or
Case When @bmth<=2 Then (month(bdate)>=1 and month(bdate)<=2 and year(bdate)=@byear))dt
Else (month(bdate)<=@bmth and year(bdate)=@byear))dt
End
where q1_revenue<>0
group by cons,firstname,lastname
order by q1_revenue desc
End

drop table #tq1_csm_rank


Please let me know what's wrong here?

Thanks.

 

[mrees]

If you think about a CASE statement, they only return 1 value for each row, you can't use them as you have tried, to use one in a WHERE clause you need something like this:

CASE 
  WHEN mydata <= 2 THEN month(mydate1)
  ELSE month(mydate2)
END
  >= 1

I wouldn't know where to start to apply this to your logic as I don't know the data. But I hope this puts you on the right track.

mrees

 

[geminihk]

Thanks mrees.