0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

-- PostHTML: HTML code to search for (T-SQL LIKE pattern, \ escapes)
-- ExcludeHTML: HTML code to exclude (T-SQL LIKE pattern, \ escapes)
-- Author: Numeric user ID of post author (0 = any author)
-- StartDate: Earliest posting date (YYYY-MM-DD)
-- EndDate: Latest posting date (YYYY-MM-DD)
-- MaxResults: Maximum number of result rows

with Matched as (
  select * from Posts
  where Body like ##PostHTML:string?%search%## escape char(92)
    and Body not like ##ExcludeHTML:string?<nothing>## escape char(92)
)
select top ##MaxResults:int?100##
  Post.Id as [Post Link],
  PostTypes.Name as [Type],
  case
    when Author.Id is null then Post.OwnerDisplayName
    else 'site://users/' + cast(Author.Id as varchar) + '|' + Author.DisplayName
  end as [Author],
  Post.Score,
  Post.CreationDate,
  coalesce(Post.ViewCount, Parent.ViewCount) as [Views],
  coalesce(Post.Tags, Parent.Tags) as [Tags]
from Matched as Post
  join PostTypes on Post.PostTypeId = PostTypes.Id
  left join Posts as Parent on Post.ParentId = Parent.Id
  left join Users as Author on Post.OwnerUserId = Author.Id
where
  (##Author:int?0## = 0 or Post.OwnerUserId = ##Author:int?0##)
  and Post.CreationDate between ##StartDate:string?2000-01-01## and ##EndDate:string?2999-12-31## + ' 23:59:59'

fork query

edited oct 28

created oct 28 JJJ