Q&A for spacecraft operators, scientists, engineers, and enthusiasts
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;