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 Id), 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, '|', c.Text) AS [Comment Link], c.* FROM ModNominations mn JOIN Comments c ON mn.Id = c.PostId ORDER BY Score;