1

Please login or register to vote for this query.

(click on this box to dismiss)

All sites by the percentage of the count of the total given votes in the last month, compared to their most active month (in the sense of total votes)

Q&A for professionals and students of engineering

DECLARE @LastMonth DATETIME;

SELECT @LastMonth = DATEADD(MONTH, -1, CURRENT_TIMESTAMP);
SELECT @LastMonth = 
  DATEFROMPARTS(
    YEAR(@LastMonth),
    MONTH(@LastMonth),
    1);

CREATE TABLE #Sites (
  DbName VARCHAR(MAX) NOT NULL,
  Month DATETIME NOT NULL,
  Count INT NOT NULL
);

DECLARE @query VARCHAR(MAX) = '';

DECLARE dbcursor CURSOR FOR
SELECT dbid, name
FROM sysdatabases
WHERE
  name NOT IN (
    'master', 'tempdb', 'model' ,'msdb', 'Data.StackExchange'
  )
  AND (
    ##WithMetas:int?1## <> 0
    OR
    name NOT LIKE '%.Meta'
  );

DECLARE @DbName VARCHAR(MAX);
DECLARE @DbId INT;

OPEN dbcursor;
FETCH NEXT FROM dbcursor INTO @DbId, @DbName;

WHILE @@FETCH_STATUS = 0
BEGIN
  IF @query <> ''
    SET @query = @query + '
    UNION ALL
    ';

  SELECT @query = @query + '
    SELECT
      ''' + @DbName + ''' AS DbName,
      DATEFROMPARTS(
        YEAR(CreationDate),
        MONTH(CreationDate),
        1) AS Month,
      COUNT(*) AS Count
    FROM [' + @DbName + '].dbo.Votes
    GROUP BY
      DATEFROMPARTS(
        YEAR(CreationDate),
        MONTH(CreationDate),
        1)
  ';

  FETCH NEXT FROM dbcursor INTO @DbId, @DbName;
END

INSERT INTO #Sites EXEC(@query);

WITH AllMonth AS (
  SELECT
    RANK() OVER (PARTITION BY DbName ORDER BY Count DESC, Month DESC)
      AS Rank,
    *
  FROM #Sites
)
SELECT
  AllMonth.DbName AS Site,
  AllMonth.Month AS MaxMonth,
  AllMonth.Count AS MaxCount,
  Last.Count AS LastCount,
  100.0 * Last.Count / AllMonth.Count AS Pct
FROM AllMonth, #Sites Last
WHERE
  AllMonth.DbName = Last.DbName
  AND
  Last.Month = @LastMonth
  AND
  AllMonth.Rank = 1
ORDER BY Pct;

fork query

edited apr 15 20

created apr 14 20 peterh (2)