0

Please login or register to vote for this query.

(click on this box to dismiss)

For each day, show how many upvotes received on that day were for questions/answers in the past week, and how many were on older posts

Q&A for speakers of other languages learning English

-- Show upvotes on recent posts versus old posts
-- For each day, show how many upvotes received on that day were for questions/answers in the past week, and how many were on older posts

CREATE TABLE #days(Date datetime)
INSERT INTO #days SELECT TOP 90 Votes.CreationDate FROM Votes, Users WHERE Users.Id = ##UserId## AND Votes.CreationDate >= Users.CreationDate GROUP BY(Votes.CreationDate) ORDER BY Votes.CreationDate DESC

CREATE TABLE #rep(Date datetime, RecentUpvotes int, OldUpvotes int)
DECLARE c CURSOR FOR SELECT * FROM #days ORDER BY Date;
OPEN c

DECLARE @day datetime
FETCH NEXT FROM c INTO @day
WHILE @@fetch_status = 0 BEGIN
WITH v AS (SELECT Votes.CreationDate AS VoteDate, Posts.CreationDate AS PostDate FROM Votes, Posts WHERE Votes.VoteTypeId = 2 AND Votes.PostId = Posts.Id AND Posts.OwnerUserId = ##UserId## AND CAST(FLOOR(CAST(Votes.CreationDate AS float)) AS datetime) = @day)
INSERT INTO #rep VALUES(@day, (SELECT COUNT(*) FROM v WHERE VoteDate <= PostDate + 7), (SELECT COUNT(*) FROM v WHERE VoteDate > PostDate + 7))
FETCH NEXT FROM c INTO @day
END

CLOSE c
DEALLOCATE c
SELECT * FROM #rep;

fork query

created jun 21 10 Michael Mrozek