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

CREATE TABLE #Elections
(
    ElectionId INT,
    StartDate  DATE,
    EndDate    DATE
);

INSERT INTO
    #Elections
VALUES
    (1, '2011-01-18', '2011-02-02'),
    (2, '2011-11-07', '2011-11-22'),
    (3, '2012-06-04', '2012-06-19'),
    (4, '2013-02-25', '2013-03-12'),
    (5, '2014-02-10', '2014-02-25'),
    (6, '2015-04-06', '2015-04-21'),
    (7, '2015-11-09', '2015-11-24'),
    (8, '2016-11-07', '2016-11-22'),
    (9, '2017-07-17', '2017-08-01'),
    (10, '2018-03-12', '2018-03-27'),
    (11, '2019-03-04', '2019-03-19'),
    (12, '2020-07-06', '2020-07-21'),
    (13, '2021-10-18', '2021-10-26'),
    (14, '2022-11-07', '2022-11-22'),
    (15, '2024-02-27', '2024-03-13');


WITH ModNominations AS (
    SELECT *
    FROM
        Posts p
            JOIN #Elections e ON p.CreationDate BETWEEN e.StartDate AND e.EndDate
    WHERE
        PostTypeId = 6
    )
SELECT
    CONCAT('site://election/', mn.ElectionId, '?tab=nomination#comment', c.Id, '_', mn.Id) AS [Comment Link],
    c.*
FROM
    ModNominations mn
        JOIN Comments c ON mn.Id = c.PostId
ORDER BY CreationDate;

fork query

edited mar 2

created mar 2 Henry.Ecker