Lengths for the consecutive edits

1

Please login or register to vote for this query.

(click on this box to dismiss)

The Workplace Meta

Q&A about the site for members of the workforce navigating the professional setting

; 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=5) AND (ph2.PostHistoryTypeId=5) 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 Lenght],
  CAST((LEN(ph2.Text)-LEN(ph1.Text)) AS int) [Difference], 
  ph2.Comment,
  ph2.UserId AS [User Link],
  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

Enter Parameters

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