1

Please login or register to vote for this query.

(click on this box to dismiss)

It shows, how many questions, answers and comments did you post, grouped by the hour of the week. The week starts with monday. 1 grid column = 1 day. To do so, the chart library was hacked to "see" 25-hour days (there is no data for the "padding" hour).

Q&A for amateur radio enthusiasts

DECLARE @Entities TABLE (
  DbName VARCHAR(MAX),
  EntityType VARCHAR(MAX),
  EntityId INT,
  CreationDate DATETIME
)

DECLARE @DbName VARCHAR(MAX)
DECLARE @Query VARCHAR(MAX)

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


OPEN DbCursor;
FETCH NEXT FROM DbCursor INTO @DbName;

WHILE @@FETCH_STATUS = 0
BEGIN
  SELECT @Query = '
    SELECT
      ''' + @DbName + ''' AS DbName,
      PostTypes.Name AS EntityType,
      PostsWithDeleted.Id AS EntityId,
      PostsWithDeleted.CreationDate AS CreationDate
    FROM
      [' + @dbName + '].dbo.PostsWithDeleted,
      [' + @dbName + '].dbo.PostTypes,
      [' + @dbName + '].dbo.Users
    WHERE
    Users.AccountId = ' + CAST(##SEAccountId:int## AS VARCHAR(MAX)) + '
      AND
      PostsWithDeleted.OwnerUserId = Users.Id
      AND
      PostTypes.Id = PostsWithDeleted.PostTypeId
      AND
      PostsWithDeleted.PostTypeId IN (1, 2)
    
    UNION
    
    SELECT
      ''' + @DbName + ''' AS DbName,
      ''Comment'' AS EntityType,
      Comments.Id AS EntityId,
      Comments.CreationDate AS CreationDate
    FROM
      [' + @dbName + '].dbo.Users,
      [' + @dbName + '].dbo.Comments
    WHERE
      Users.AccountId = ' + CAST(##SEAccountId## AS VARCHAR(MAX)) + '
      AND
      Users.Id = Comments.UserId
    
    UNION
    
    SELECT DISTINCT
      ''' + @DbName + ''' AS DbName,
      ''Edit'' AS EntityType,
      PostHistory.Id AS EntityId,
      PostHistory.CreationDate AS CreationDate
    FROM
      [' + @DbName + '].dbo.PostHistory,
      [' + @DbName + '].dbo.Users
    WHERE
      PostHistory.UserId = Users.Id
      AND
      Users.AccountId = ' + CAST(##SEAccountId## AS VARCHAR(MAX)) + '
      AND
      PostHistory.PostHistoryTypeId IN (4, 5, 6)
  ';
  INSERT INTO @Entities EXEC(@Query);
  
  FETCH NEXT FROM dbCursor INTO @dbName;
END

CLOSE dbCursor;

--SELECT * FROM @Entities;
WITH Raw AS (
  SELECT
    ((DATEPART(DW, CreationDate) + 5) % 7) * 25 +
      DATEPART(HOUR, CreationDate) AS HourOfWeek,
    EntityType
  FROM
    @Entities
)
SELECT
  HourOfWeek,
  EntityType,
  COUNT(*) AS N
FROM
  Raw
GROUP BY HourOfWeek, EntityType

UNION

SELECT 225 AS HourOfWeek, 'Padding', 0
  
ORDER BY HourOfWeek, EntityType

fork query

edited dec 10 17

created oct 10 17 peterh