1

Please login or register to vote for this query.

(click on this box to dismiss)

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

-- Temporary table to store the results
CREATE TABLE #results (
  Site NVARCHAR(250),
  PostId INT,
  Title NVARCHAR(250),
  Tags NVARCHAR(250),
  Score INT,
  TagName NVARCHAR(50),
  Asked DATETIME,
  MarkedForReview DATETIME
);

-- We'll need (at least) one INSERT statement for each site, they'll end up in this variable
DECLARE @sql NVARCHAR(MAX) = '';

-- Generate INSERT statements:
-- - Use the database's `name` to indicate the site (this can be converted to site name later on)
-- - Fully qualify table names
SELECT @sql = @sql + 'INSERT INTO #results' + 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) + N'.dbo.PostHistory AS ph
      WHERE ph.PostId = p.Id
        AND ph.PostHistoryTypeId = 6 -- Edit Tags
        AND (ph.Text LIKE N''%<status-review>%''
          OR ph.Text LIKE N''%<estado-en-revisión>%''
          OR ph.Text LIKE N''%<調査中>%''
          OR ph.Text LIKE N''%<status-revisão>%''
          OR ph.Text LIKE N''%<статус-проверка>%'')
      ORDER BY CreationDate ASC
  ) ph
  OUTER APPLY (
    SELECT TOP 1 t.TagName
      FROM ' + QUOTENAME(name) + '.dbo.PostTags AS pt
      INNER JOIN ' + QUOTENAME(name) + N'.dbo.Tags AS t
        ON pt.TagId = t.Id
      WHERE pt.PostId = p.Id
        AND (t.TagName LIKE N''status-%''
          OR t.TagName LIKE N''estado-%''
          OR t.TagName IN (N''仕様'', N''完了'', N''却下'', N''延期'', N''再現性なし'', N''対応予定'', N''再現済'', N''調査中'')
          OR t.TagName LIKE N''статус-%'')
  ) t
  WHERE ph.CreationDate BETWEEN ''' + ##fromDate:string## + '''
                            AND ''' + ##tillDate:string## + '''
'
FROM sys.databases
WHERE database_id > 5 -- that's where the site databases begin
  AND name LIKE '%.Meta'

-- execute it
EXEC (@sql)
-- Site details, cf. https://meta.stackexchange.com/a/359795/295232
CREATE TABLE #Sites (URL NVARCHAR(100), SiteName NVARCHAR(100), DatabaseName NVARCHAR(50));
-- fetch Markdown for post containing table with site details
DECLARE @md NVARCHAR(MAX);
SELECT TOP 1 @md = Text
  FROM [StackExchange.Meta].dbo.PostHistory
  WHERE PostId = 359796 AND PostHistoryTypeId = 5
  ORDER BY CreationDate DESC;
-- split Markdown on pipe symbol
CREATE TABLE #TableEntries (ID INT IDENTITY(1, 1), Value NVARCHAR(100));
INSERT INTO #TableEntries (Value)
SELECT LEFT(TRIM(VALUE), 100) FROM STRING_SPLIT(@md, '|')
  WHERE LEN(TRIM(REPLACE(REPLACE(VALUE, CHAR(10), ''), CHAR(13), ''))) > 0;
-- 'pivot' - we have to discard the introductory text, headers and horizontal lines
INSERT INTO #Sites
SELECT t0.Value, t1.Value, t2.Value
  FROM #TableEntries AS t0
  INNER JOIN #TableEntries AS t1
    ON t0.Id + 1 = t1.Id
  INNER JOIN #TableEntries AS t2
    ON t0.Id + 2 = t2.Id
  WHERE t0.Id >= 8 -- that's where the real content starts
    AND t0.Id % 3 = 2;
    
SELECT s.SiteName,
  CONCAT(s.URL, '/q/', r.PostId, '|', r.Title) AS 'Question',
  Tags, Score, TagName,
  Asked AS 'Question asked',
  MarkedForReview AS '[status-review] added'
  FROM #results AS r
  INNER JOIN #Sites AS s
    ON r.Site = s.DatabaseName
  ORDER BY MarkedForReview DESC;
     
DROP TABLE #results;

fork query

edited sep 1 23

created may 31 20 Glorfindel