1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for people who use Blender to create 3D graphics, animations, or games

---the query looks at the last revision with the given tag
---and than the revision after that
---mostly that could correspond to "tag cleaner"
---in some cases the post where the tag was created might have been deleted
; with tagshistory_cte AS
(
SELECT
  Id AS phid,
  replace(value,'>','') TagName
FROM
  PostHistory
  CROSS APPLY STRING_SPLIT(Text,'<')
WHERE PostHistoryTypeId in (3,6,9)
and len(value) > 0
), tagslast_cte AS (
SELECT
  tagname,
  MAX(phid) AS lastid
FROM tagshistory_cte
GROUP BY tagname
), emptytags_cte AS(
SELECT
  tl.tagname AS tagname,
  tl.lastid AS lastid
  FROM tagslast_cte tl LEFT OUTER JOIN Tags t ON tl.tagname=t.TagName
  WHERE (t.Tagname IS NULL)
), emptytagspid_cte AS (
SELECT
  e.tagname AS tagname,
  e.lastid AS lastid,
  ph.postid AS pid
FROM emptytags_cte e INNER JOIN PostHistory ph ON ph.id=e.lastid
), tagsfirst_cte AS (
SELECT
  e.tagname AS tagname,
  MIN(ph.Id) AS firstid
FROM emptytagspid_cte e INNER JOIN PostHistory ph ON ph.PostId=e.pid
  WHERE (ph.PostHistoryTypeId in (3,6,9)) AND (ph.Id>e.lastid)
GROUP BY e.tagname
)

SELECT
  t.tagname,
  ph.CreationDate,
  ph.UserId AS [User Link], 
  url = 'site://posts/' + CAST(ph.PostId AS varchar)+'/revisions',
  ph.PostId AS [Post Link],
  ph.Comment
FROM tagsfirst_cte t INNER JOIN PostHistory ph ON ph.id=t.firstid
WHERE ph.UserId=##UserId##

fork query

edited jun 15

created jun 15 Martin20528