Q&A for administrators and users of the CiviCRM Constituent Relationship Management software
Select Username as [User Link], CappedRep+BadgeCount as Total, CappedRep, BadgeCount, ModBadges, EdBadges "EditingBadges", ParticipationBadges From ( Select u.Id As Username, (case when u.Reputation > 20000 then 20000 else u.Reputation end)/1000 As CappedRep, Count(distinct case when b.Name in ( 'Civic Duty', 'Cleanup', 'Constituent', 'Convention', 'Copy Editor', 'Deputy', 'Electorate', 'Enthusiast', 'Explainer', 'Investor', 'Marshal', 'Organizer', 'Quorum', 'Refiner', 'Sportsmanship', 'Strunk & White', 'Tag Editor', 'Reviewer', 'Steward', 'Yearling') then b.Name else null end) As BadgeCount, count(distinct iif(b.Name in ('Civic Duty', 'Cleanup', 'Deputy', 'Electorate', 'Marshal', 'Sportsmanship', 'Reviewer', 'Steward'), b.Name, null)) ModBadges, count(distinct iif(b.Name in ( 'Constituent', 'Convention', 'Enthusiast', 'Investor', 'Quorum', 'Yearling'), b.Name, null)) ParticipationBadges, count(distinct iif(b.Name in ( 'Organizer', 'Copy Editor', 'Explainer', 'Refiner', 'Tag Editor', 'Strunk & White'), b.Name, null)) EdBadges From Users u left join Badges b on b.UserId=u.Id Where u.Id in (##UserIds##) Group By u.Id, u.Reputation ) cscores Order By Total desc