16

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for pro webmasters

-- Editor, Strunk & White, Copy Editor

;with Requirements(Description, Requirement) as
(
    select 'Editor'        ,   1 union all
    select 'Strunk & White',  80 union all
    select 'Copy Editor'   , 500
)
select [User Link]   = ##UserId##,
       [Edits]       = c.countRevisions,
       [Badge]       = r.description,
       [Requirement] = r.Requirement,
       [To Go]       = (select max(ToGo) from (values
                         (r.Requirement - countRevisions),
                         (0)) [ ] (ToGo))
from
(
    select count(DISTINCT ph.RevisionGUID) as countRevisions
    from PostHistory ph
    where ph.PostHistoryTypeId in (4,5) -- check in PostHistoryTypes for these magic numbers
      and ph.UserId = ##UserId##
      and not exists (select * from Posts p       
                      where p.OwnerUserId = ph.UserId and p.Id = ph.PostId)
) c
inner loop join Requirements r on 1=1
order by r.Requirement desc

fork query

created feb 25 11 cyberkiwi2010