Stack Exchange new posts/month

1

Please login or register to vote for this query.

(click on this box to dismiss)

Chess

Q&A for serious players and enthusiasts of chess

DECLARE @output TABLE (
  DbName VARCHAR(max),
  Month DATE,
  Count INT);

DECLARE @query VARCHAR(MAX);

DECLARE dbcursor CURSOR FOR
SELECT name
FROM sysdatabases
WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb', 'Data.StackExchange')
AND LOWER(name) LIKE 'stackexchange.%';

DECLARE @dbname VARCHAR(max);

OPEN dbcursor;
FETCH NEXT FROM dbcursor INTO @dbname;

WHILE @@FETCH_STATUS = 0
BEGIN
  SELECT @query = '
    WITH t AS (
      SELECT
        DATEFROMPARTS(
          DATEPART(yyyy, CreationDate),
          DATEPART(mm, CreationDate),
        1) AS Month
      FROM [' + @dbname + '].dbo.PostsWithDeleted
    )
    SELECT
      ''' + @dbname + ''' AS dbname,
      Month, COUNT(*) AS N
    FROM t
    GROUP BY Month;';
  INSERT INTO @output EXEC (@query);
  FETCH NEXT FROM dbcursor INTO @dbname;
END

CLOSE dbcursor;

SELECT Month, SUM(Count) AS Count
FROM @output
GROUP BY Month
ORDER BY Month;

Enter Parameters

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