0

Please login or register to vote for this query.

(click on this box to dismiss)

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

-- Grab list of voters
DECLARE @election_start_date datetime = ##ElectionStartDate:string##
DECLARE @next_election_start_date datetime = ##NextElectionStartDate:string##

SELECT 
  u.AccountId AS [AccountId]
INTO
  #voters
FROM
  Users AS u
  INNER JOIN Badges AS b
    ON u.Id = b.UserId
WHERE
  b.Name = 'Constituent'
  AND DATEDIFF(MINUTE, @election_start_date, b.Date) > 0
  AND DATEDIFF(MINUTE, DATEADD(DAY, -1, @next_election_start_date), b.Date) < 0

-- Generate name of meta database
DECLARE @metadbname nvarchar(max) = DB_NAME() + '.Meta'

DECLARE @query nvarchar(max) = '
  USE [' + @metadbname + ']
  
  SELECT
    u.Reputation,
    u.UpVotes + u.DownVotes AS [Votes],
    (SELECT COUNT(p.Id) 
     FROM Posts AS p 
     WHERE p.OwnerUserId = u.Id 
           AND p.CreationDate < ''' + CAST(@election_start_date AS nvarchar) + ''') AS [Posts],
    (SELECT COUNT(c.Id) 
     FROM Comments AS c 
     WHERE c.UserId = u.Id 
           AND c.CreationDate < ''' + CAST(@election_start_date AS nvarchar) + ''') AS [Comments]
  FROM
    Users AS u
  WHERE
    u.AccountId IN (SELECT AccountId FROM #voters)
  '

EXEC sp_executesql @query
--PRINT @query

fork query

edited dec 29 14

created dec 29 14 senshin