## "Home site" of election voters

1

(click on this box to dismiss)

Q&A for people studying math at any level and professionals in related fields

-- Grab list of voters
SELECT
u.AccountId AS [AccountId]
INTO
#voters
FROM
Users AS u
ON u.Id = b.UserId
WHERE
b.Name = 'Constituent'
AND DATEDIFF(MINUTE, '2014-12-14', b.Date) > 0

-- Create cursor for list of sites
DECLARE sites CURSOR FOR
SELECT name
FROM sys.databases
WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb', 'Data.StackExchange')
AND (name = 'StackExchange.Meta' OR name NOT LIKE '%.Meta') -- exclude per-site metas
-- And declare some variables
DECLARE @sitedbname AS nvarchar(max)
DECLARE @sitehostname AS nvarchar(max)
-- These variables are "optional" but useful
DECLARE @query AS nvarchar(max)
CREATE TABLE #out (
SiteName nvarchar(max),
AccountId int,
SiteReputation int
)
-- These variables are for the SPOOKY HOSTNAME GENERATION CODE
DECLARE @spooky_string AS nvarchar(max)
DECLARE @spooky_delimiter AS char(1) = '.'
DECLARE @spooky_xml AS xml
DECLARE @spooky_result AS nvarchar(max)

-- Step through sites cursor
OPEN sites
FETCH NEXT FROM sites INTO @sitedbname
WHILE @@FETCH_STATUS = 0
BEGIN
-----------------------------------------------------------------------------
-- BEGIN SPOOKY HOSTNAME GENERATION CODE ------------------------------------
-----------------------------------------------------------------------------
SET @spooky_string = @sitedbname
SET @spooky_xml = CAST(('<X>' + REPLACE(@spooky_string, @spooky_delimiter, '</X><X>') + '</X>') AS xml)
SET @spooky_result = ''

SELECT
C.value('.', 'nvarchar(max)') AS [Piece],
C.value('for \$i in . return count(../*[. << \$i]) + 1', 'int') AS [Index]
INTO #spooky_pieces
FROM @spooky_xml.nodes('X') AS X(C)

SELECT @spooky_result = COALESCE(@spooky_result + '.', '') + [Piece]
FROM #spooky_pieces
ORDER BY [Index] DESC

DROP TABLE #spooky_pieces

SET @sitehostname = 'http://' + RIGHT(@spooky_result, LEN(@spooky_result)-1) + '.com'
----------------------------------------
-- HERE COME THE SPOOKY SPECIAL CASES --
----------------------------------------
-- Meta MathOverflow doesn't have a redirect; see <http://meta.stackexchange.com/q/215071/224428>
IF @sitedbname = 'StackExchange.Mathoverflow.Meta' SET @sitehostname = 'http://Meta.MathOverflow.net'
-- For some reason probably involving the AVP/Audio/Video/Sound hullabaloo, there is
-- still a StackExchange.Audio DB that's getting updated. http://audio.stackexchange.com/
-- no longer exists, so we use Video.SE for the hostname instead.
IF @sitedbname = 'StackExchange.Audio' SET @sitehostname = 'http://Video.StackExchange.com'
-----------------------------------------------------------------------------
-- END SPOOKY HOSTNAME GENERATION CODE --------------------------------------
-----------------------------------------------------------------------------

SET @query = '
USE [' + @sitedbname + ']

INSERT INTO #out
SELECT TOP 10
''' + @sitedbname + ''' AS [SiteName],
AccountId AS [AccountId],
Reputation AS [SiteReputation]
FROM
Users
WHERE
AccountId IN (SELECT [AccountId] FROM #voters)
'
--PRINT @query
EXEC sp_executesql @query

FETCH NEXT FROM sites INTO @sitedbname
END
CLOSE sites
DEALLOCATE sites

-- Reap results (also optional)
SELECT
COUNT(*)
FROM
#out

### Enter Parameters

Options:
-
Switch to meta site
Hold tight while we fetch your results
:records returned in :time ms:cached