Number of Re-Opened question vs. RO with no-edits vs. RO with edits by others vs. RO edits by OP

0

Please login or register to vote for this query.

(click on this box to dismiss)

Craft CMS Meta

Q&A about the site for administrators, end users, developers and designers for Craft CMS

-- XXX BROKEN
-- PostHistoryTypes: 11 - Post Reopened
with ReOpenedQuestions as (
  select p.CreationDate, p.LastEditDate, p.OwnerUserId, p.LastEditorUserId
  from Posts p, PostHistory ph
  where p.Id = ph.PostId and ph.PostHistoryTypeId = 11 and p.PostTypeId = 1 -- question
  and ph.CreationDate is not null and p.OwnerUserId is not null and p.ClosedDate is null
  and (p.LastEditDate is null or p.LastEditDate < ph.CreationDate)
  )
  select 
    (select count(*) from ReOpenedQuestions) as [re-opened questions], 
    (select count(*) from ReOpenedQuestions where LastEditDate is null) as [re-opened with no-edits],
    (select count(*) from ReOpenedQuestions where LastEditDate is not null and LastEditDate < CreationDate and OwnerUserId != LastEditorUserId) as [re-opened with edits by others],
    (select count(*) from ReOpenedQuestions where LastEditDate is not null and LastEditDate < CreationDate and OwnerUserId = LastEditorUserId) as [re-opened with edits by OP]

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results