Upvotes on answers compared

0

Please login or register to vote for this query.

(click on this box to dismiss)

Mathematics

Q&A for people studying math at any level and professionals in related fields

DECLARE @columns NVARCHAR(MAX), @columnSums NVARCHAR(MAX);
SELECT @columns =
  STRING_AGG(QUOTENAME(Id), ',')
  WITHIN GROUP (ORDER BY Score DESC)
  FROM Posts
  WHERE ParentId = ##QuestionId:int##;
SELECT @columnSums =
  STRING_AGG('SUM(p2.' + QUOTENAME(Id) + ') AS '
    + QUOTENAME(CAST(Id AS NVARCHAR) + ': ' + CAST(Score AS NVARCHAR)), ',')
  WITHIN GROUP (ORDER BY Score DESC)
  FROM Posts
  WHERE ParentId = ##QuestionId:int##;

DECLARE @sql NVARCHAR(MAX) = '
WITH PivotTable AS (SELECT CreationDate, ' + @columns + '
FROM (SELECT v.PostId, v.CreationDate
  FROM Votes AS v
  INNER JOIN Posts AS a
    ON v.PostId = a.Id
  WHERE v.VoteTypeId = 2 -- Up
    AND a.ParentId = ' + CAST(##QuestionId:int## AS NVARCHAR) + '
    AND v.CreationDate BETWEEN ''' + ##FromDate:string## + '''
                           AND ''' + ##TillDate:string## + ''') AS Temp
PIVOT (COUNT(PostId) FOR PostId IN (' + @columns + ')) AS Temp2)
SELECT p1.CreationDate, ' + @columnSums + '
  FROM PivotTable AS p1
  INNER JOIN PivotTable AS p2
    ON p1.CreationDate >= p2.CreationDate
  GROUP BY p1.CreationDate
  ORDER BY p1.CreationDate';

EXEC (@sql);

Enter Parameters

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