>Hey All
>
>I'm trying to do a 'count by item by user' based on if the item was returned to the user for editing. The result I'm hoping for is something like this:
>
>

>Cat     User       User Name       Good Item Count    Returned Item Count
>40       DAB      Dorris Beaird              22                         5
>

>
>here's my SQL Statement out of the scratch query window
>
>

>declare @Whr nvarchar(100), @sqlCmd nvarchar(650), @Init2 nchar(3), @mbdate date, @medate date
>set @mbdate = '2012-05-01'
>set @medate = '2012-06-01'
>
>set @Whr =  ' rtrim(e.EnteredBy) = ''DAB '' and e.entereddate between ''' + cast(@mbdate as NCHAR(10)) + ''' and ''' +  cast(@medate as nchar(10)) + ''''
>
>set @SqlCmd = 'Select e.Category, e.EnteredBy, 
>  FullName = r.CheckID,FullName = rtrim(u.UserLastName) + '', ''  +  u.UserFirstName, 
>  case r.checkid
>    when 0 then COUNT(r.checkid) as chkcnt
>    when -1 then Count(r.checkid) as revcnt
>  end
>  from ExpMaster as e, ReviewHistory as r, UserFile as u
>where r.XPID = e.origid
>  and u.UserInit = e.EnteredBy
>  and ' + 
>  @Whr + 
>' 
>group by e.Category, e.EnteredBy, r.CheckID, rtrim(u.UserLastName) + '', ''  +  u.UserFirstName
>order by e.EnteredBy'
>print @sqlCmd
>Execute (@SqlCmd)
>

>
>It's giving me "Incorrect syntax near the keyword 'as'" on line 4. What should the correct syntax be, assuming that I can actually do what I want to do?

First of all, there is no need to use dynamic SQL in your case. Change it to

Select e.Category, e.EnteredBy, 
r.CheckID,rtrim(u.UserLastName) + ','  +  u.UserFirstName as FullName, 
COUNT(  case when r.checkid =0 then 1 end)  as chkcnt
COUNT(case when r.CheckId= -1 then 1 end) as revcnt
  end
from ExpMaster as e

INNER JOIN ReviewHistory as r
ON  r.XPID = e.origid
INNER JOIN UserFile as u
ON u.UserInit = e.EnteredBy
WHERE e.EnteredBy = 'DAB' and e.EnteredDate between @mbDate and @mbEDate
group by e.Category, e.EnteredBy, r.CheckID, u.UserLastName, u.UserFirstName
order by e.EnteredBy'