Q&A for researchers, developers, students, teachers, and end users interested in bioinformatics
-- DECLARE @Keyword nvarchar(100) = '%##LowerCaseKeyword##%' -- user prompt DECLARE @Keyword nvarchar(30) = '%graph%' -- leading/trailing space helps match stackoverflow.com search behavior -- Build the filter result set; contains key and unanswered 'skew' value CREATE TABLE #unanswered (Id int primary key, Age int, UnansweredSkew int) INSERT #unanswered SELECT q.Id as Id, CAST((GETDATE() - q.CreationDate) AS INT) as Age, CASE WHEN q.AcceptedAnswerId is null THEN -10 WHEN q.AcceptedAnswerId is not null THEN 0 END AS [Total] FROM Posts q WHERE ((q.Tags LIKE '%adal%') OR (q.Tags LIKE '%office365%') OR (q.Tags LIKE '%azure-active-directory%')) AND ((LOWER(q.Body) LIKE @Keyword) OR(LOWER(q.Title) LIKE @Keyword)) -- Build the weighting result set, using the one above as driver SELECT p.Id AS [Post Link], p.ViewCount, p.AnswerCount, CONVERT(VARCHAR(10), p.CreationDate, 1) as Created, u.Age, CASE WHEN p.AcceptedAnswerId is null THEN 'false' ELSE 'true' END AS Answered, ((p.ViewCount * .05) + (u.Age * .1) + p.AnswerCount + u.UnansweredSkew) AS Weight FROM Posts p JOIN #unanswered u ON u.Id = p.Id ORDER BY Answered ASC, Weight DESC