>Cat User User Name Good Item Count Returned Item Count >40 DAB Dorris Beaird 22 5 >>
>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) >>
case r.checkid when 0 then COUNT(r.checkid) as chkcnt when -1 then Count(r.checkid) as revcnt endwith
COUNT(case WHEN r.checkid=0 THEN 1 END) as chkcnt, Count(case WHEN r.checkid=-1 THEN 1 END) as revcnt