Count votes per day

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

SELECT 
    CONVERT(varchar, v.CreationDate, 23) as 'date',
    count(v.Id) as q_vote_count 
INTO #q_votes
FROM Votes v
INNER JOIN posts p
ON (p.Id = v.PostId AND p.PostTypeId = 1)
GROUP BY CONVERT(varchar, v.CreationDate, 23);

SELECT 
    CONVERT(varchar, v.CreationDate, 23) as 'date',
    count(v.Id) as a_vote_count 
INTO #a_votes
FROM Votes v
INNER JOIN posts p
ON (p.Id = v.PostId AND p.PostTypeId = 2)
GROUP BY CONVERT(varchar, v.CreationDate, 23);

SELECT q.date, q.q_vote_count, a.a_vote_count
FROM #q_votes q
INNER JOIN #a_votes a
ON (q.date = a.date);

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results