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

replace

  case r.checkid
    when 0 then COUNT(r.checkid) as chkcnt
    when -1 then Count(r.checkid) as revcnt
  end

with

    COUNT(case WHEN r.checkid=0 THEN 1 END) as chkcnt,
    Count(case WHEN r.checkid=-1 THEN 1 END) as revcnt