1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

-- How close are you to getting the Archaeologist badge?
--  "Edited 100 posts that were inactive for 6 months"

DECLARE @page INT = ##pageNumber##;

WITH OrderedPosts AS (

SELECT ROW_NUMBER() OVER (ORDER BY p.Score ASC) AS RowNumber,
       p.Id AS [Post Link], 
       p.Score, 
       p.LastActivityDate 
FROM Posts p
WHERE p.PostTypeId = 1 -- Questions only
 AND (
   p.LastEditDate < DATEADD(month, -6, CURRENT_TIMESTAMP) AND
   p.LastActivityDate < DATEADD(month, -6, CURRENT_TIMESTAMP)
 )
 AND NOT EXISTS (
     SELECT * FROM PostHistory h
     WHERE PostId = p.Id
       AND h.CreationDate > DATEADD(month, -6, CURRENT_TIMESTAMP)
 )
 AND NOT EXISTS (
     SELECT * FROM Posts c
     WHERE c.ParentId = p.Id
      AND (
        c.LastEditDate > DATEADD(month, -6, CURRENT_TIMESTAMP) OR
        c.LastActivityDate > DATEADD(month, -6, CURRENT_TIMESTAMP)
      )
 ) 
 AND p.Score >= 0
 -- Only recent posts (don't bring up things from 2008)
 AND p.LastActivityDate > DATEADD(month, -8, CURRENT_TIMESTAMP)
)
SELECT TOP (20) *
FROM OrderedPosts
WHERE RowNumber BETWEEN (@page - 1) * 20 AND @page * 20

fork query

edited apr 18 13 Troy Alford

created may 9 12 GregB