2

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

-- In the Query box, have something like:
--     select count(*) from [meta]..Posts where creationDate >= '1 jan 2015'
-- You can make the query reference [meta] or [site] tables.
-- The query has to be relatively simple.... or it won't complete.
-- the query will be run on each site (substituting the [meta] or [site] as needed)

declare SITES cursor for
    select Substring(Name, 0, Len(Name) - 4),
           Name
    from sys.Databases
    where Name like '%.Meta'
     and not Name = 'StackExchange.Meta'
--UNION
--    select 'StackExchange.Meta'

create table #sitestats (
    Site NVarchar(250),
    SName NVarchar(100),
    Elections NVarchar(10),
    Metric Integer);
    
declare @sitedbname as NVarchar(100),
        @sitemetaname as NVarchar(100),
        @query as NVarchar(1000),
        @metric as integer,
        @elections as NVarchar(10)
    ;
    
declare @target as NVarchar(100) = ##SiteName:string?StackExchange.CodeReview##;
    
open SITES

fetch next from SITES into @sitedbname, @sitemetaname
while @@FETCH_STATUS = 0
BEGIN

    declare @org as NVarchar(100), @http as NVarchar(100), @pos as int;

    -- convert the DBName in to a URL for the result set.
    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'

    --print 'Processing site ' + @sitedbname + ' -> ' + @http
    
    --Add whatever sub-URL you want to the http url:
    select @http = @http + '/users/'
    
    insert into #sitestats
    values (@http + '|' + @sitedbname, @sitedbname, 'No', 0)

    -- build and run your dynamic query. Note how you prefix the tables you need
    
    select @query = ##Query:string##;
    select @query = REPLACE(@query, '[site]', '[' + @sitedbname + ']');
    select @query = REPLACE(@query, '[meta]', '[' + @sitemetaname + ']');
    
    set @query = '
           update #sitestats
           set Metric = (' + @query + '),
               Elections = case when exists (
                     select 1 from [' + @sitedbname + ']..Badges where Name = ''Caucus''
                   ) then ''Full'' else ''Beta'' end
           where SName = '''  + @sitedbname + ''' '; 
           
    EXEC sp_executesql @query

    fetch next from SITES into @sitedbname, @sitemetaname
END

close SITES
deallocate SITES
;

select @metric = Metric
from #sitestats
where SName = @target
;



With TypeTotals as (
    select Elections,
           count(*) as Total,
           Sum(case when Metric <= @metric then 1 else 0 end) as LessThan
    from #sitestats
    group by Elections)
select @target as Target,
       @metric as Metric,
       Elections as Elections,
       LessThan,
       Total,
       Cast((100.0 * LessThan) / Total as Decimal(10,2)) as PercentLess
from TypeTotals

select * from #sitestats order by metric DESC, Elections Desc;

fork query

edited aug 25 15

created jun 18 15 rolfl