2

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for science fiction and fantasy enthusiasts

select 'site://posts/' + cast(PostId as nvarchar) + '/revisions|'
    + Posts.Title as [Post Revisions],
    ph.Text as Tags,
    (case when ph.UserId is not null then
     'site://users/' + cast(ph.UserId as nvarchar) + '|' else '' end) +
     coalesce(ph.UserDisplayName, Users.DisplayName) as [User Link],
    ph.CreationDate,
    pht.Name as [Edit Type]
from PostHistory ph
left join Posts on ph.PostId = Posts.Id
left join Users on ph.UserId = Users.Id
left join PostHistoryTypes pht on PostHistoryTypeId = pht.Id
where PostHistoryTypeId in (3, 6, 9) -- initial tags, edit tags
  and Text like '%<##TagName##>%'
  and (UserId = ##UserId:int?0## or ##UserId## = 0)
  and ((ph.PostHistoryTypeId = 3 and ##MatchInitialTags:int?1## = 1)
    -- match tag edits where this tag was added
    or (select top 1 Text
        from PostHistory prev
        where prev.Id < ph.Id and prev.PostId = ph.PostId
          and PostHistoryTypeId in (3, 6, 9)
        order by prev.Id desc) not like '%<##TagName##>%')
order by ph.Id desc

fork query

edited jun 12 16

created may 27 16 amaranth