1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for power users of Apple hardware and software

DECLARE @SEAccountId INT;

IF ##SiteAccountId:string?-## = '-'
  SET @SEAccountId = CAST(##NetworkAccountId:string?-## AS INT)
ELSE
  SELECT @SEAccountId = AccountId
  FROM Users
  WHERE Id = CAST(##SiteAccountId## AS INT);

SELECT @SEAccountId;

/*
DECLARE @output TABLE (
  DbName VARCHAR(MAX),
  Month DATETIME,
  Questions INT,
  Answers INT,
  Comments INT,
  Edits INT,
  Badges INT);

DECLARE @query VARCHAR(MAX);

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

DECLARE @dbname VARCHAR(max);

OPEN dbcursor;
FETCH NEXT FROM dbcursor INTO @dbname;

WHILE @@FETCH_STATUS = 0
BEGIN
  SELECT @query = '
    SELECT
      ''' + @dbname + ''' AS DbName,
      Users.DisplayName AS UserName,
      Users.AccountId AS SEAccountId,
      Users.Id AS SiteAccountId,
      Badges.Date AS BadgeDate
    FROM [' + @dbname + '].dbo.Users, [' + @dbname + '].dbo.Badges
    WHERE
      Badges.UserId = Users.Id
      AND
      Badges.Name = ''Steward''
  ';
  INSERT INTO @output EXEC (@query);
  FETCH NEXT FROM dbcursor INTO @dbname;
END

CLOSE dbcursor;

WITH WithBadgeNo AS (
  SELECT
    COUNT(*) OVER (PARTITION BY SEAccountId) AS BadgeNo,
    *
  FROM @output
),
WithRank AS (
  SELECT
    DENSE_RANK() OVER (ORDER BY BadgeNo DESC) AS Rank,
    *
  FROM WithBadgeNo
)
SELECT
  'http://stackexchange.com/users/'
    + CAST(SEAccountId AS VARCHAR(30))
    + '?tab=accounts|' + UserName AS 'SE Account',
  'https://data.stackexchange.com/stackoverflow/query/678540?AccountId='
    + CAST(SEAccountId AS VARCHAR(30)) + '#resultSets|' + UserName
    AS [With Hidden Accounts],
  *
FROM WithRank
ORDER BY Rank, SEAccountId, BadgeDate;

view current version

edited may 31 21

created may 31 21 peterh (2)