total rep across the network for one account


Please login or register to vote for this query.

(click on this box to dismiss)

Raspberry Pi

Q&A for users and developers of hardware and software for Raspberry Pi

---- accountid: Account on ! "In the url on"

-- create url from dbname
IF OBJECT_ID ( '#siteurl', 'P' ) IS NOT NULL 
    DROP PROCEDURE #siteurl;

create procedure #siteurl
   @dbname nvarchar(250),
   @res nvarchar(250) OUT
      declare @pos integer
      declare @rec nvarchar(250)
      set @res = @dbname
      set @pos = CHARINDEX('.', @dbname)
      if (@pos > 0 ) 
         set @rec = substring(@dbname, @pos+1, len(@dbname))
         exec #siteurl @rec,  @rec output
         set @res = @rec 
                  + '.' 
                  + substring(@dbname, 0, @pos) 

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
                         and name not like '%.Meta%'
                         or name = 'StackExchange.Meta'

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_users ( site nvarchar(250)
                            , userid int
                            , displayname nvarchar(40)
                            , reputation int);
open db_c
fetch next from db_c into @db_c_name
while(@@FETCH_STATUS = 0)
    set @sql = N'use '+ QUOTENAME(@db_c_name) +';
               declare @url nvarchar(250)
               exec #siteurl ''' + @db_c_name  + ''', @url output
               insert into #all_users 
               select @url
               , u.displayname
               , u.reputation
               from users u  
               where u.accountid = '+ cast(@accountid as nvarchar) + ';'
    exec (@sql)
   fetch next from db_c into @db_c_name
close db_c;
deallocate db_c;

select 'all ' as [site]
      , 'networkwide' as [display name]
      , sum(reputation)
from #all_users
union all
select 'all ' as [site]
      , 'badgeable' as [display name]
      , sum(reputation)
from #all_users
where reputation>200
union all
select replace(site,'.StackExchange','') as [site]
     , 'http://' 
     + site 
     + '.com/'
     + 'users'
     + '/'
     + cast(userid as nvarchar)
     + '|'
     + displayname
     , reputation
from #all_users
order by 3 desc

drop table #all_users

Enter Parameters

Switch sites:
loading Hold tight while we fetch your results