0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for Ubuntu users and developers

DECLARE @Days INT = ##Days##;
-- Days: Past Days "Enter the number of past days to search"

SELECT TOP 100
    q.Id AS [Post Link],
    q.ViewCount AS Views,
    vUp.UpvoteCount AS [Upvotes],
    vDown.DownvoteCount AS [Downvotes],
    q.Score,
    q.CreationDate,
    q.Tags
FROM
    Posts q
LEFT JOIN 
    (SELECT PostId, COUNT(*) AS UpvoteCount
     FROM Votes
     WHERE VoteTypeId = 2  -- 2 represents upvotes
     GROUP BY PostId) vUp
ON q.Id = vUp.PostId
LEFT JOIN 
    (SELECT PostId, COUNT(*) AS DownvoteCount
     FROM Votes
     WHERE VoteTypeId = 3  -- 3 represents downvotes
     GROUP BY PostId) vDown
ON q.Id = vDown.PostId
WHERE
    q.PostTypeId = 1
    AND DATEDIFF(day, q.CreationDate, GETDATE()) <= @Days
ORDER BY
    q.Score DESC;

fork query

created may 31 anonymous