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 #ElectionPostRanges (
  ElectionId INT,
  StartDate Date,
  EndDate Date
  --FirstNominationId INT NULL,
  --LastNominationId INT NULL
);
INSERT INTO #ElectionPostRanges 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')
;
SELECT
  C.Id AS [Comment Link],
  C.Score,
  C.CreationDate,
  P.Id AS [Post Link]
FROM Comments C
JOIN Posts P ON P.Id = C.PostId
JOIN #ElectionPostRanges R ON P.CreationDate BETWEEN R.StartDate AND R.EndDate
WHERE
  P.PostTypeId = 6 -- nomination
  AND C.Score >= 10
ORDER BY C.Score DESC

fork query

edited mar 2

created mar 2 anonymous