1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for scientists using computers to solve scientific problems

; with nextedit_cte as
(SELECT
  ph1.Id AS phid,
  MIN(ph2.Id) AS nextid
FROM PostHistory ph1 INNER JOIN PostHistory ph2 ON ph1.PostId=ph2.PostId
WHERE (ph1.PostHistoryTypeId IN (2,5,8)) AND (ph2.PostHistoryTypeId IN (5,8)) AND (ph1.Id<ph2.Id)
---5 = Edit Body - modified post body (raw markdown)
GROUP BY ph1.Id
)

SELECT TOP(##num?500##)
  ph1.PostId AS [Post Link],
  concat('site://posts/',
          ph1.PostId,
          '/revisions'
          ) [Revisions Link],
  LEN(ph1.Text) [Old Length], LEN(ph2.Text) [New Length],
  CAST((LEN(ph2.Text)-LEN(ph1.Text)) AS int) [Difference], 
  CAST(ABS(LEN(ph2.Text)-LEN(ph1.Text)) AS int) [Difference], 
  ph2.Comment,
    ph2.CreationDate
FROM PostHistory ph1 INNER JOIN nextedit_cte ne ON ph1.Id=ne.phid
  INNER JOIN PostHistory ph2 ON ph2.Id=ne.nextid
ORDER BY ph2.CreationDate DESC

fork query

created jun 16 Martin20528