Q&A for committed Christians, experts in Christianity and those interested in learning more
/* Inspiration for cross-site query from: Mark Hurd, http://data.stackexchange.com/stackoverflow/query/69700/generate-emailhash-comparison Tim Stone, http://data.stackexchange.com/stackoverflow/query/72411 */ /* We build a dynamic query as a prelude, then @Template for each site with @Separator in between, and finally a postlude. First, the template, which may contain the following escape strings: ~n the "pretty" site name (or close enough), e.g. 'Dba' ~u the URL of the site, e.g. http://dba.stackexchange.com ~~ a literal '~' */ create table #badges ( BadgeName NVarchar(50) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL); insert into #badges values ('Rank'); insert into #badges values ('Legendary'); insert into #badges values ('Epic'); insert into #badges values ('Fanatic'); insert into #badges values ('Marshal'); insert into #badges values ('Generalist'); insert into #badges values ('Sportsmanship'); insert into #badges values ('Strunk & White'); insert into #badges values ('Copy Editor'); insert into #badges values ('Steward'); insert into #badges values ('Electorate'); create table #sitestats ( Site NVarchar(250), BadgeName NVarchar(50), Awards int); declare @sitedbname as NVarchar(100), @query as NVarchar(1000) ; declare SITES cursor for select Substring(Name, 0, Len(Name) - 4 ) from sys.Databases where Name like '%.Meta' --and Name = 'StackExchange.Codereview.Meta' and not Name = 'StackExchange.Meta' UNION select 'StackExchange.Meta' open SITES fetch next from SITES into @sitedbname while @@FETCH_STATUS = 0 BEGIN declare @org as NVarchar(100), @http as NVarchar(100), @pos as int; select @org = lower(@sitedbname), @http = 'http://', @pos = CharIndex('.', @sitedbname); if @pos > 0 begin select @http = @http + substring(@org, @pos + 1, len(@org) - @pos) + '.', @org = left(@org, @pos - 1) end select @http = @http + @org + '.com/help/badges' print 'Processing site ' + @sitedbname + ' -> ' + @http set @query = ' insert into #sitestats select ''' + @http + '|' + @sitedbname + ''', BadgeName, Count(UserId) FROM #badges left outer join [' + @sitedbname + ']..Badges on BadgeName = Name Group by BadgeName' ; print @query EXEC sp_executesql @query fetch next from SITES into @sitedbname END close SITES deallocate SITES ; with Rankings as ( select Site as Site, BadgeName as BadgeName, (select count (*) from #sitestats yy where yy.BadgeName = xx.BadgeName and yy.Awards > xx.awards) as Rank from #sitestats xx ), AvgRanks as ( select Site as Site, Avg(Rank) as Overall from Rankings group by Site ) insert into #sitestats select Site, 'Rank', 1 + (select count(*) from AvgRanks sub where sup.Overall > sub.Overall) from AvgRanks sup select * from (select * from #sitestats) as ss PIVOT (SUM(ss.Awards) FOR ss.BadgeName IN ( [Rank], [Legendary], [Epic], [Fanatic], [Marshal], [Generalist], [Sportsmanship], [Strunk & White], [Copy Editor], [Steward], [Electorate] ) ) AS BadgePivot