What question has the most cycles of Close/Reopen networkwide ?

0

Please login or register to vote for this query.

(click on this box to dismiss)

https://meta.stackexchange.com/q/277175/158100

Stack Overflow

Q&A for professional and enthusiast programmers

-- result table, don't rename and keep the site column
create table #results ( site nvarchar(250)
                      , postid int
                      , title nvarchar(250)
                      , cls int
                      , opn 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 + '''
     , id
     , title
     , [closed]
     , [re-opened]
from ' + quotename(name) + '.dbo.posts p
inner join (
select top 10 postid
     , [10] as [closed]
     , [11] as [re-opened]
     from ( select postid, posthistorytypeid 
            from ' + quotename(name) + '.dbo.posthistory
            where posthistorytypeid in (10,11) --close/reopen
           ) as src
pivot ( 
  count(posthistorytypeid)
  for posthistorytypeid
  in ([10], [11])
) as pvt
order by [10]+[11] desc
) ph on ph.postid = p.id
-- order  by [closed]+ [re-opened] desc
' 
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  site , 
       -- URL builder from Lamak http://stackoverflow.com/a/38129701
       'http://' 
      + case PARSENAME(site,1)
        when 'audio' then 'avp'
        else PARSENAME(site,1)
        end 
      + coalesce('.' 
        + case PARSENAME(site,2)
          when 'audio' then 'avp'
          else PARSENAME(site,2)
          end , '')
      + IIF(patindex('%.MathOverflow%',site) > 0
      , ''
      , coalesce('.' + PARSENAME(site,3), '')
      )
      + IIF(patindex('%.MathOverflow%',site) > 0
      , '.net'
      , '.com'
      ) 
      + '/q/'
      + cast(postid as nvarchar)
      + '|'
      + title 
      -- rest of columns
      site
      , [cls]
      , [opn]
from #results
order by [cls] + [opn] desc

drop table #results

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results