Cross-network mercenaries

1

Please login or register to vote for this query.

(click on this box to dismiss)

People by MIN(OfferedRep, WonRep), Network-wide

Raspberry Pi

Q&A for users and developers of hardware and software for Raspberry Pi

DECLARE @bounty TABLE (
  DbName VARCHAR(MAX) NOT NULL,
  UserName VARCHAR(MAX) NOT NULL,
  AccountId INT NOT NULL,
  Rep INT NOT NULL
);

DECLARE @query VARCHAR(MAX);

DECLARE dbcursor CURSOR FOR
SELECT name
FROM sysdatabases
WHERE
  name NOT IN ('master', 'tempdb', 'model' ,'msdb', 'Data.StackExchange')
  AND
  name NOT LIKE '%Temp%';

DECLARE @dbname VARCHAR(MAX);

OPEN dbcursor;
FETCH NEXT FROM dbcursor INTO @dbname;

WHILE @@FETCH_STATUS = 0
BEGIN
  SELECT @query = '
    SELECT
      ''' + @dbname + ''' AS DbName,
      MIN(Users.DisplayName) AS UserName,
      Users.AccountId,
      SUM(Votes.BountyAmount) AS Rep
    FROM
      [' + @dbName + '].dbo.Votes,
      [' + @dbName + '].dbo.Posts,
      [' + @dbName + '].dbo.Users
    WHERE
      Votes.VoteTypeId = 9
      AND
      Votes.BountyAmount > 0
      AND
      Votes.PostId = Posts.Id
      AND
      Posts.OwnerUserId = Users.Id
      AND
      Posts.PostTypeId = 2
    GROUP BY Users.AccountId
  ';
  INSERT INTO @bounty EXEC (@query);
  FETCH NEXT FROM dbcursor INTO @dbname;
END

CLOSE dbcursor;

SELECT
  MIN(UserName) AS UserName,
  AccountId,
  SUM(Rep) AS Rep
FROM @bounty
GROUP BY AccountId
ORDER BY Rep DESC, AccountId;

Enter Parameters

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