Posts with [status-review], network-wide

0

Please login or register to vote for this query.

(click on this box to dismiss)

Meta Stack Exchange

Q&A for meta-discussion of the Stack Exchange family of Q&A websites

-- result table, don't rename and keep the site column
CREATE TABLE #results (site NVARCHAR(250),
                       postid INT,
                       title NVARCHAR(250),
                       tags NVARCHAR(250),
                       score INT,
                       tagname NVARCHAR(50),
                       asked DATETIME,
                       markedForReview DATETIME);

DECLARE @sql NVARCHAR(MAX) = ''   -- holds build up sql string

-- build one biq union sql, for each db
SELECT @sql = @sql 
+ IIF( LEN(@sql) > 1 
     , 'UNION ALL'
     , 'INSERT INTO #results'
) +
-- here goes the per site query, fully qualify the database objects
N'
SELECT ''' + name + ''', p.Id, p.Title, p.Tags, p.Score, t.TagName,
  p.CreationDate, ph.CreationDate
  FROM ' + quotename(name) + '.dbo.Posts AS p
  CROSS APPLY (
    SELECT TOP 1 CreationDate
      FROM ' + quotename(name) + '.dbo.PostHistory AS ph
      WHERE ph.PostId = p.Id
        AND ph.PostHistoryTypeId = 6 -- Edit Tags
        AND ph.Text LIKE ''%<status-review>%''
      ORDER BY CreationDate ASC
  ) ph
  OUTER APPLY (
    SELECT TOP 1 t.TagName
      FROM ' + quotename(name) + '.dbo.PostTags AS pt
      INNER JOIN ' + quotename(name) + '.dbo.Tags AS t
        ON pt.TagId = t.Id
      WHERE pt.PostId = p.Id
        AND t.TagName LIKE ''status-%''
  ) t
  WHERE ph.CreationDate BETWEEN ''' + ##fromDate:string## + '''
                            AND ''' + ##tillDate:string## + '''
'
FROM sys.databases
WHERE database_id > 5
  AND name LIKE '%.Meta'

-- execute it
EXEC (@sql)

SELECT site,
  LOWER('https://' +
    IIF(PATINDEX('%.Mathoverflow%', site) > 0,
    IIF(PATINDEX('%.Meta', site) > 0, 'meta.mathoverflow.net', 'mathoverflow.net'),
      IIF(PATINDEX('%.Ubuntu%', site) > 0,
      IIF(PATINDEX('%.Meta', site) > 0, 'meta.askubuntu.com', 'askubuntu.com'),
        IIF(PATINDEX('StackExchange.%', site) > 0,
          CASE SUBSTRING(site, 15, 200)
          WHEN 'Audio' THEN 'video'
          WHEN 'Audio.Meta' THEN 'video.meta'
          WHEN 'Beer' THEN 'alcohol'
          WHEN 'Beer.Meta' THEN 'alcohol.meta'
          WHEN 'CogSci' THEN 'psychology'
          WHEN 'CogSci.Meta' THEN 'psychology.meta'
          WHEN 'Garage' THEN 'mechanics'
          WHEN 'Garage.Meta' THEN 'mechanics.meta'
          WHEN 'Moderators' THEN 'communitybuilding'
          WHEN 'Moderators.Meta' THEN 'communitybuilding.meta'
          WHEN 'Photography' THEN 'photo'
          WHEN 'Photography.Meta' THEN 'photo.meta'
          WHEN 'Programmers' THEN 'softwareengineering'
          WHEN 'Programmers.Meta' THEN 'softwareengineering.meta'
          WHEN 'Vegetarian' THEN 'vegetarianism'
          WHEN 'Vegetarian.Meta' THEN 'vegetarianism.meta'
          WHEN 'Writers' THEN 'writing'
          WHEN 'Writers.Meta' THEN 'writing.meta'
          ELSE SUBSTRING(site, 15, 200)
          END + '.stackexchange.com',
          IIF(PATINDEX('StackOverflow.%', site) > 0,
            CASE SUBSTRING(site, 15, 200)
            WHEN 'Br' THEN 'pt'
            WHEN 'Br.Meta' THEN 'pt.meta'
            ELSE SUBSTRING(site, 15, 200)
            END + '.stackoverflow.com',
            IIF(PATINDEX('%.Meta', site) > 0,
              'meta.' + SUBSTRING(site, 0, PATINDEX('%.Meta', site)) + '.com',
              site + '.com'
            )
          )
        )
      )
      )) + '/q/' + CAST(postid AS NVARCHAR) + '|' + title AS 'Question',
  tags, score, tagname,
  asked AS 'Question asked',
  markedForReview AS '[status-review] added'
  FROM #results
  ORDER BY markedForReview DESC
     
DROP TABLE #results

Enter Parameters

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