Thanks, I knew it was something simple, just couldn't find it.


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