6

Please login or register to vote for this query.

(click on this box to dismiss)

Gets: Score, Age, Views, and Anonymous Feedback for a specific post and any answers it may have. (If given an answer-post ID, it just fetches data for that answer.)

Q&A for cartographers, geographers and GIS professionals

DECLARE @PostId INT = ##PostId##;

WITH AnonVoteCounts AS (
    SELECT      pf.PostId
                , SUM (CASE WHEN pf.VoteTypeId = 2 THEN 1 ELSE 0 END)
                AS Helpful
                , SUM (CASE WHEN pf.VoteTypeId = 3 THEN 1 ELSE 0 END)
                AS Unhelpful
    FROM        PostFeedback pf
    GROUP BY    pf.PostId
)
SELECT      p.Id     AS [Post Link]
            ,   CASE    p.PostTypeId
                WHEN    1   THEN 'Q'
                WHEN    2   THEN 'A'
                ELSE    '?'
                END AS [Q/A]
            , p.Score
            , avc.Helpful
            , avc.Unhelpful
            , p.ViewCount
            --, CONVERT (VARCHAR(25), p.CreationDate, 100)
            , DATEDIFF (dd, p.CreationDate, GETDATE() ) AS [Days old]
FROM        Posts p
LEFT JOIN   AnonVoteCounts  avc
ON          p.Id        = avc.PostId
WHERE       p.Id IN (
    SELECT              q.id
    FROM                Posts   q
    WHERE               q.id    = @PostId
    UNION ALL SELECT    a.Id
    FROM                Posts   a
    WHERE               a.ParentId  = @PostId
)
ORDER BY    P.Id

fork query

edited jun 27 12

created jun 27 12 Awesome Poodles