1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for Joomla! administrators, users, developers and designers

;with cw_cte as
(SELECT
  ph.Id AS cwid,
  ph.PostId AS pid
FROM PostHistory ph INNER JOIN Posts p ON ph.PostId=p.Id
WHERE (ph.PostHistoryTypeId=16)
---16 = Community Owned - post now community owned
)

SELECT TOP(##num?100##)
---  COUNT (DISTINCT RevisionGUID) AS [All edits],
  COUNT (DISTINCT ph.Id) AS [All edits],
---notice that title/body/tags edits are counted separately
---so a single edit which changed title, body and tags will count as three edits
  SUM(CASE WHEN (ph.Id<c.cwid) THEN 1 ELSE 0 END) AS [Edits before CW],
  SUM(CASE WHEN (ph.Id>c.cwid) THEN 1 ELSE 0 END) AS [Edits after CW],
  ph.PostId AS [Post Link],
  revisions = 'site://posts/' + CAST(ph.PostId AS varchar)+'/revisions'
FROM cw_cte c INNER JOIN PostHistory ph ON (ph.PostId=c.pid) 
WHERE (ph.PostHistoryTypeId in (1,2,3,4,5,6,7,8,9))
---only edits
GROUP BY ph.PostId
ORDER BY ph.PostId DESC

fork query

edited jun 17

created jun 17 Martin20528