0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for builders and users of networked sensors and control devices in the contexts of smart homes, industry automation, or environmental sensors

SELECT Id AS [Post Link], (SELECT MAX(v) FROM (VALUES (ClosedDate), (LastEditDate)) AS value(v)) AS MaxDate
FROM Posts
WHERE CONVERT(date,ClosedDate) <= DATEADD(d, -7, CONVERT(date,GETUTCDATE()))
AND (CONVERT(date,LastEditDate) <= DATEADD(d, -7, CONVERT(date,GETUTCDATE()))
         OR LastEditDate IS NULL)
--  AND CreationDate >= DATEADD(d, -30, CONVERT(date,GETUTCDATE()))
  AND Score <= 1
  AND AcceptedAnswerId IS NULL
  -- Exclude positive answers
  AND NOT EXISTS (SELECT * FROM Posts p2 WHERE p2.ParentId = Posts.Id AND Score > 0)
  -- Exclude closed as duplicate
  AND NOT EXISTS (SELECT * FROM PostLinks WHERE PostId = Posts.Id AND LinkTypeId = 3)
  -- Exclude migrated and merged
  AND NOT EXISTS (SELECT * FROM PostHistory WHERE PostId = Posts.Id AND PostHistoryTypeId IN (17,18,35,37))
ORDER BY 2

fork query

created feb 22 18 anonymous