SELECT TOP 100 p.Id AS id, REPLACE(p.Title, ',', '@@@@') ...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Aviation

Q&A for aircraft pilots, mechanics, and enthusiasts

SELECT TOP 100
      p.Id AS id,
      REPLACE(p.Title, ',', '@@@@') AS title,
      p.Tags AS tags,
      CONVERT(VARCHAR(20), p.CreationDate, 120) AS creation_date, 
      CONVERT(VARCHAR(20), p.LastActivityDate, 120) AS last_activity_date,
      ISNULL(p.FavoriteCount, 0) AS favourite_count, 
      ISNULL(p.ViewCount, 0) AS view_count,
      ISNULL(v.UpVote, 0) AS up_votes, 
      ISNULL(v.DownVote, 0) AS down_votes,
      REPLACE(REPLACE(p.Body, CHAR(13), '\r\n'), CHAR(10), '\r\n') AS body
FROM
(SELECT Votes.PostID, 
        UpVote = SUM(CASE WHEN VoteTypeId = 2 THEN 1 ELSE 0 END), 
        DownVote = SUM(CASE WHEN VoteTypeId = 3 THEN 1 ELSE 0 END) 
        FROM Votes WHERE VoteTypeId in (2,3) GROUP BY PostId) v
RIGHT JOIN Posts p ON v.PostID = p.id
WHERE p.PostTypeId = 1
ORDER BY p.Id DESC

Enter Parameters

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