0

Please login or register to vote for this query.

(click on this box to dismiss)

Close votes and what happens afterwards

Q&A about the site for people organizing, marketing or licensing open source development projects

-- Close-edit-reopen cycle

select
  closed.PostId as PostId,
  closed.CreationDate as CloseDate,
  (select
    min(opened.CreationDate)
    from PostHistory opened
    where opened.PostId = closed.PostId
      and opened.CreationDate > closed.CreationDate
      and opened.PostHistoryTypeId = 11) as ReopenDate,
  (select
    count(*)
    from PostHistory edited join PostHistory opened on edited.PostId = opened.PostId
    where edited.PostId = closed.PostId
      and edited.CreationDate > closed.CreationDate
      and edited.CreationDate < opened.CreationDate
      and edited.PostHistoryTypeId = 5
      and opened.PostHistoryTypeId = 11) as EditedCount
  from PostHistory closed
  where PostHistoryTypeId = 10

fork query

edited jul 17 12 David Zaslavsky

created dec 29 10 Jon Seigel