0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for contractors and serious DIYers

WITH a AS (
    SELECT
        -- Calculate the number of days since previous mod nomination
        -- if it's more than 30 days since the last nomination consider it a new election by marking a 1
        IIF(DATEDIFF(DD, LAG(CreationDate) OVER (ORDER BY CreationDate), CreationDate) > 30, 1, 0) AS [NewElectionFlag],
        *
    FROM
        Posts p
    WHERE
        PostTypeId = 6
    ),
     ModNominations AS (
         SELECT
             -- Take cumulative sum of flags to build election IDs
             SUM(NewElectionFlag) OVER (ORDER BY CreationDate) + 1 AS [ElectionNumber],
             *
         FROM
             a
         )
SELECT
    mn.ElectionNumber,
    CONCAT('site://election/', mn.ElectionNumber, '?tab=nomination#comment', c.Id, '_', mn.Id) AS [Comment Link],
    c.*
FROM
    ModNominations mn
        JOIN Comments c ON mn.Id = c.PostId
ORDER BY Score;

fork query

edited mar 2 anonymous

created mar 2 Henry.Ecker