current statuses of posts linked to a certain FAQ post, grouped by creation date

1

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

DECLARE @DupTarget AS int = ##DupTarget:int?25385173## -- default: debugger FAQ

-- current statuses of any linked posts grouped by _creation_ date
--  Note the funny (and sad) spikes during the eternal septembers
SELECT
  MAX(P.CreationDate),
  MAX(P.Status),
  COUNT(*)
FROM (
SELECT
  DATEADD(MONTH, DATEDIFF(MONTH, 0, P.CreationDate), 0) AS CreationDate,
  CASE
    WHEN P.DeletionDate IS NOT NULL THEN '2:deleted'
    WHEN P.ClosedDate   IS NOT NULL THEN '1:closed'
    ELSE                                 '0:open'
  END AS Status
FROM PostLinks L
JOIN PostsWithDeleted P ON P.Id = L.PostId
WHERE
  L.RelatedPostId = 25385173
  --AND L.LinkTypeId = 3 -- 3 means dup, 1 means non-dup comment
) P
GROUP BY P.CreationDate, P.Status
;

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results