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.
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.