Your questions where you could accept, but you didn't (network-wide)

1

Please login or register to vote for this query.

(click on this box to dismiss)

Computational Science

Q&A for scientists using computers to solve scientific problems

DECLARE @output TABLE (
  DbName VARCHAR(MAX),
  PostId INT,
  Title VARCHAR(MAX),
  Score INT,
  AnswerCount INT,
  CreationDate DATETIME
);

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,
      Posts.Id AS PostId,
      Posts.Title,
      Posts.Score,
      Posts.AnswerCount,
      Posts.CreationDate
    FROM
      [' + @DbName + '].dbo.Posts,
      [' + @DbName + '].dbo.Users
    WHERE
      Posts.AnswerCount > 0
      AND
      Posts.OwnerUserId = Users.Id
      AND
      Posts.AcceptedAnswerId IS NULL
      AND
      Users.AccountId = ' + CAST(##SEAccountId:int## AS VARCHAR(MAX))+ ';';
  INSERT INTO @output EXEC (@query);
  FETCH NEXT FROM dbcursor INTO @DbName;
END

CLOSE dbcursor;

WITH Site2Db AS (
  SELECT
    name AS DbName,
    LOWER(CASE
      WHEN
        name = 'StackExchange.Programmers'
      THEN
        'softwareengineering.stackexchange.com'
      WHEN
        name = 'StackExchange.Programmers.Meta'
      THEN
        'softwareengineering.meta.stackexchange.com'
      WHEN
        name LIKE 'StackExchange.%'
      THEN
        RIGHT(name, LEN(name) - 14) + '.stackexchange.com'
      WHEN
        name = 'StackOverflow.Br'
      THEN
        'pt.stackoverflow.com'
      WHEN
        name = 'StackOverflow.Br.Meta'
      THEN
        'pt.meta.stackoverflow.com'
      WHEN
        name LIKE 'StackOverflow.__'
      THEN
        RIGHT(name, 2) + '.stackoverflow.com'
      WHEN
        name LIKE 'StackOverflow.__.Meta'
      THEN
        RIGHT(name, 7) + '.stackoverflow.com'
      WHEN
        name = 'StackOverflow'
      THEN
        'stackoverflow.com'
      WHEN
        name = 'StackOverflow.Meta'
      THEN
        'meta.stackoverflow.com'
      WHEN
        name = 'StackApps'
      THEN
        'stackapps.com'
      WHEN
        name = 'ServerFault'
      THEN
        'serverfault.com'
      WHEN
        name = 'ServerFault.Meta'
      THEN
        'meta.serverfault.com'
      WHEN
        name = 'SuperUser'
      THEN
        'superuser.com'
      WHEN
        name = 'SuperUser.Meta'
      THEN
        'meta.superuser.com'
      ELSE
        NULL
      END
    ) AS SiteName
  FROM sysdatabases
  WHERE
    name NOT IN ('master', 'tempdb', 'model' ,'msdb', 'Data.StackExchange')
)
SELECT
  Site2Db.SiteName AS Site,
  'https://' + SiteName + '/questions/' + CAST(PostId AS VARCHAR(MAX))
    + '|' + Title AS PostLink,
  Score,
  AnswerCount,
  CreationDate
FROM @output AS Output
LEFT JOIN Site2Db ON Output.DbName = Site2Db.DbName
ORDER BY CreationDate, Output.DbName, PostId;

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results