0

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

CREATE TABLE #results (site NVARCHAR(250),
  ReviewsCompletedPeriod INT,
  ReviewsCompletedComparisonPeriod INT,
  MoreReviewsCompleted FLOAT,
  ReviewsCreatedPeriod INT,
  ReviewsCreatedComparisonPeriod INT,
  MoreReviewsCreated FLOAT);
  
DECLARE @sql NVARCHAR(MAX) = ''

SELECT @sql = @sql 
+ IIF( LEN(@sql) > 1 
     , 'UNION ALL'
     , 'INSERT INTO #results'
) +
N'
SELECT ''' + name + ''',
  ReviewsCompletedPeriod, ReviewsCompletedComparisonPeriod,
  CASE ReviewsCompletedComparisonPeriod WHEN 0 THEN NULL ELSE
  ROUND((ReviewsCompletedPeriod - ReviewsCompletedComparisonPeriod) * 100.0 / ReviewsCompletedComparisonPeriod, 2) END,
  ReviewsCreatedPeriod, ReviewsCreatedComparisonPeriod,
  CASE ReviewsCreatedComparisonPeriod WHEN 0 THEN NULL ELSE
  ROUND((ReviewsCreatedPeriod - ReviewsCreatedComparisonPeriod) * 100.0 / ReviewsCreatedComparisonPeriod, 2) END
FROM (
SELECT (
  SELECT COUNT(*) FROM ' + quotename(name) + '.dbo.ReviewTasks
    WHERE ReviewTaskTypeId = ' + CAST(##reviewTaskTypeId:int## AS NVARCHAR) + '
      AND DeletionDate BETWEEN ''' + ##beginOfPeriod:string## + ''' AND ''' + ##endOfPeriod:string## + '''
      AND CompletedByReviewTaskId IS NOT NULL
) AS ReviewsCompletedPeriod, (
  SELECT COUNT(*) FROM ' + quotename(name) + '.dbo.ReviewTasks
    WHERE ReviewTaskTypeId = ' + CAST(##reviewTaskTypeId:int## AS NVARCHAR) + '
      AND DeletionDate BETWEEN ''' + ##beginOfComparedPeriod:string## + ''' AND ''' + ##endOfComparedPeriod:string## + '''
      AND CompletedByReviewTaskId IS NOT NULL
) AS ReviewsCompletedComparisonPeriod, (
  SELECT COUNT(*) FROM ' + quotename(name) + '.dbo.ReviewTasks
    WHERE ReviewTaskTypeId = ' + CAST(##reviewTaskTypeId:int## AS NVARCHAR) + '
      AND CreationDate BETWEEN ''' + ##beginOfPeriod:string## + ''' AND ''' + ##endOfPeriod:string## + '''
) AS ReviewsCreatedPeriod, (
  SELECT COUNT(*) FROM ' + quotename(name) + '.dbo.ReviewTasks
    WHERE ReviewTaskTypeId = ' + CAST(##reviewTaskTypeId:int## AS NVARCHAR) + '
      AND CreationDate BETWEEN ''' + ##beginOfComparedPeriod:string## + ''' AND ''' + ##endOfComparedPeriod:string## + '''
) AS ReviewsCreatedComparisonPeriod
) AS Temp
'
FROM sys.databases
WHERE database_id > 5 AND name NOT LIKE '%.Meta';
-- reviewTaskTypeId: Queue: 1=Suggested Edits, 2=Close Votes, 3=Low Quality Posts, 4=First Posts, 5=Late Answers, 6=Reopen Votes

EXEC (@sql);

SELECT * FROM (
SELECT '_TOTAL' AS 'site',
  SUM(ReviewsCompletedPeriod) AS '# completed in period',
  SUM(ReviewsCompletedComparisonPeriod) AS '# completed compared',
  ROUND((SUM(ReviewsCompletedPeriod) - SUM(ReviewsCompletedComparisonPeriod)) * 100.0 / SUM(ReviewsCompletedComparisonPeriod), 2) AS '% more completed',
  SUM(ReviewsCreatedPeriod) AS '# created in period',
  SUM(ReviewsCreatedComparisonPeriod) AS '# created compared',
  ROUND((SUM(ReviewsCreatedPeriod) - SUM(ReviewsCreatedComparisonPeriod)) * 100.0 / SUM(ReviewsCreatedComparisonPeriod), 2) AS '% more created'
  FROM #results
UNION ALL
SELECT * FROM #results
) AS Temp ORDER BY SUBSTRING(site, 1, 1), 4 DESC;
  
DROP TABLE #results;

fork query

edited oct 7 19

created oct 7 19 Glorfindel