1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for professors, theologians, and those interested in exegetical analysis of biblical texts

; 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=5) AND (ph1.Id<ph2.Id)
---2 = Initial Body - initial post raw body text
---5 = Edit Body - modified post body (raw markdown)
---8 = Rollback Body - reverted body (raw markdown)
GROUP BY ph1.Id
)

SELECT TOP(##num?500##)
  ph1.PostId AS [Post Link],
  concat('site://posts/',
          ph1.PostId,
          '/revisions'
          ) [Revisions Link],  
  CAST((LEN(ph2.Text)-LEN(ph1.Text)) AS int) [Difference], 
  ph2.Comment,
  ph2.UserId AS [User Link],
  ph2.CreationDate,
  ph2.Text
FROM PostHistory ph1 INNER JOIN nextedit_cte ne ON ph1.Id=ne.phid
  INNER JOIN PostHistory ph2 ON ph2.Id=ne.nextid
WHERE (ph1.Text=ph2.Text)
  AND (ph2.Text LIKE '%##text?\*##%' COLLATE Latin1_General_CI_AI)
ORDER BY ph2.CreationDate DESC

fork query

created oct 28 23 Martin20528