5

Please login or register to vote for this query.

(click on this box to dismiss)

Gets Score over Time 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 linguists, teachers and students of the Korean language

DECLARE @PostId INT = ##PostId##;

WITH VotesByDate AS (
    SELECT
        v.CreationDate AS [Date],
        CASE    p.PostTypeId
            WHEN    1   THEN 'QST: ' + COALESCE (u.DisplayName, '*Dead User*')
            WHEN    2   THEN 'ANS: ' + COALESCE (u.DisplayName, '*Dead User*')
            ELSE    '?!?!'
        END AS PostHandle,
        SUM (
            CASE WHEN v.VoteTypeId = 2 THEN 1 ELSE -1 END
        ) AS Vote

    FROM        Votes   v
    INNER JOIN  Posts   P
    ON          p.id    = v.PostId
    LEFT JOIN   Users   u
    ON          u.id    = p.OwnerUserId
    WHERE       v.PostId IN (
        SELECT              q.id
        FROM                Posts   q
        WHERE               q.id    = @PostId
        UNION ALL SELECT    a.Id
        FROM                Posts   a
        WHERE               a.ParentId  = @PostId
    )
    AND         v.VoteTypeId IN (2, 3)
    GROUP BY    v.CreationDate, v.PostId, p.PostTypeId, u.DisplayName
)
SELECT
    p.[Date],
    p.PostHandle,
    SUM (T.Vote) AS Score
FROM        VotesByDate p
LEFT JOIN   VotesByDate T
ON          T.[Date] <= p.[Date]  AND  T.PostHandle = p.PostHandle
GROUP BY    p.[Date], p.PostHandle
ORDER BY    p.[Date], p.PostHandle

fork query

created oct 24 12 Awesome Poodles