0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for anime and manga fans

-- result table, don't rename and keep the site column
create table #results ( site nvarchar(250)
                       , username nvarchar(35)
                       , accountid int
                       , days int);

declare @sql nvarchar(max) = ''   -- holds build up sql string

-- build one biq union sql, for each db
select @sql = @sql 
+ iif( len(@sql) > 1 
     , 'union'
     , 'insert into #results'
) +
-- here goes the per site query, fully qualify the database objects
N'
select ''' + name + '''
     , u.displayname
     , u.accountid
     , datediff(d, u.creationdate, b.[date])
from ' + quotename(name) + '.dbo.badges b 
inner join ' + quotename(name) + '.dbo.users u on u.id = b.userid
where b.name = ''Legendary''
'
from sys.databases
where database_id > 5
-- and (name not like '%.Meta' or name = 'StackExchange.Meta')

--print @sql

-- execute it
exec (@sql)

-- show results
select    
       ( 'https://gis.stackexchange.com/users/' + cast(o.accountid as nvarchar)
       + '|' 
       + (select min(username) from #results r where r.accountid = o.accountid) 
       ) as [Network profile]
      , count(*) as [# times earned]
      , min(days) as [# days to reach]
from #results o
group by accountid
order by count(*) desc
       , min(days) asc

drop table #results

fork query

created nov 29 23 Kadir.ahbaz