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