http://meta.stackexchange.com/questions/246194/is-there-a-way-to-get-people-reached-sum-across-entire-network-for-a-user
Q&A for programming puzzle enthusiasts and code golfers
---- accountid: Account on stackexchnage.com ! "In the url on stackexchange.com" -- create url from dbname IF OBJECT_ID ( '#siteurl', 'P' ) IS NOT NULL DROP PROCEDURE #siteurl; GO create procedure #siteurl @dbname nvarchar(250), @res nvarchar(250) OUT as begin declare @pos integer declare @rec nvarchar(250) set @res = @dbname set @pos = CHARINDEX('.', @dbname) if (@pos > 0 ) begin set @rec = substring(@dbname, @pos+1, len(@dbname)) exec #siteurl @rec, @rec output set @res = @rec + '.' + substring(@dbname, 0, @pos) end end; go declare @accountid int = ##accountid:int## -- 93484 -- all databases declare db_c cursor for select [name] from sys.databases where database_id > 5 -- skip master, temp, model, msdb, Data.SE declare @db_c_name sysname -- holds name of db after fetch declare @sql nvarchar(max) -- holds build up sql string -- result table create table #all_reached ( site nvarchar(250) , userid int , displayname nvarchar(50) , reached bigint); open db_c fetch next from db_c into @db_c_name while(@@FETCH_STATUS = 0) begin set @sql = N'use '+ QUOTENAME(@db_c_name) +'; declare @url nvarchar(250) exec #siteurl ''' + @db_c_name + ''', @url output declare @userid int = null declare @displayname nvarchar(50) select @userid = id , @displayname = displayname from users where accountid = '+ cast(@accountid as nvarchar) + ' if @userid is not null begin insert into #all_reached select @url , @userid , @displayname , sum(isnull(p.viewcount,0)) from posts p inner join ( Select Id From Posts Where PostTypeId = 1 and owneruserid = @userid Union Select ParentId From Posts Where PostTypeId = 2 And Id in (select AcceptedAnswerId from Posts) and owneruserid = @userid Union Select ParentId From Posts Where PostTypeId = 2 And Score > 5 and owneruserid = @userid Union Select a.ParentId From Posts a Join Posts q On a.ParentId = q.Id Where a.PostTypeId = 2 And a.Score > 0.20 * (select sum(Score) from Posts where ParentId=q.Id) And a.Score > 0 and a.owneruserid = @userid Union Select x.ParentId From (Select a.ParentId, Rank() Over(Partition By a.ParentId Order By ta.Score Desc) AnswerRank From Posts a Join Posts ta On ta.ParentId = a.ParentId Where a.PostTypeId = 2 And a.Score > 0 and a.owneruserid = @userid ) x Where AnswerRank <= 3) rp on rp.id = p.id end;' exec (@sql) fetch next from db_c into @db_c_name end; close db_c; deallocate db_c; select 'all ' as [site] , 'networkwide' as [display name] , sum(reached) from #all_reached union all select replace(site,'.StackExchange','') as [site] , 'http://' + site + '.com/' + 'users' + '/' + cast(userid as nvarchar) + '|' + displayname , reached from #all_reached order by 3 desc drop table #all_reached