This one works by applying the stored procedure to the distinct reasons rather than processing the whole set.
CREATE PROC dbo.##HTMLtoMARKDOWN @text nvarchar(500),
@returnText nvarchar(500) output
AS
BEGIN
DECLARE @counter tinyint
SET @counter=1
WHILE CHARINDEX('**', @text, 1) > 0
BEGIN
SELECT @text = STUFF(@text,
CHARINDEX('**', @text, 1),
2,
IIF(@counter%2=0,'<br><b>','</b>')),
@counter = @counter + 1
END
SET @counter=1
WHILE CHARINDEX('*', @text, 1) > 0
BEGIN
SELECT @text = STUFF(@text,
CHARINDEX('*', @text, 1),
1,
IIF(@counter%2=0,'<br><i>','</i>')),
@counter = @counter + 1
END
-- SET @returnText = @text
SET @returnText = @text
END
GO
DECLARE @returnText nvarchar(500)
--taken from http://meta.stackexchange.com/a/237237/242800
;with ReasonsPerPost as
(
-- get the count of each close/flag reason per post
select TOP 100 PERCENT -- Only use with clustered index.
Posts.Id,
PendingFlags.CloseReasonTypeId,
IIF(CloseReasonTypeId<>102,CloseReasonTypes.Name,MarkdownMini) as Name,
count(PendingFlags.CloseReasonTypeId) as TotalByCloseReason
from Posts
INNER JOIN PendingFlags on PendingFlags.PostId = Posts.Id
INNER JOIN CloseReasonTypes on CloseReasonTypes.Id=PendingFlags.CloseReasonTypeId
LEFT OUTER JOIN CloseAsOffTopicReasonTypes on CloseAsOffTopicReasonTypes.id=PendingFlags.CloseAsOffTopicReasonTypeId
where Posts.ClosedDate IS NULL -- The question is not closed.
and PendingFlags.FlagTypeId in (14,13) -- Exclude reopen votes
group by Posts.id, CloseReasonTypes.Name, MarkdownMini, PendingFlags.CloseReasonTypeId
order by TotalByCloseReason desc
),
TopPerPost as
(
-- create a row number to order the results by the close reason totals
select Id,
CloseReasonTypeId,
Name,
ReasonsPerPost.TotalByCloseReason,
row_number() over(partition by Id order by TotalByCloseReason desc) seq
from ReasonsPerPost
where Name is NOT NULL
)
select TOP ##Limit:int?38369## -- This number may grow, or get removed the day the server will have enough RAM.
Posts.Id as [Post Link], -- Question title.
Count(PendingFlags.PostId) as [Number of pending flags], -- Number of pending flags per questions.
TopPerPost.Name as [The most common vote reason],
Posts.OwnerUserId as [User Link], -- Let click on the colum to see if the same user ask off-topic questions often.
Reputation as [User Reputation], -- Interesting to see that such questions are sometimes asked by high rep users.
Posts.Score as [Votes], -- Interesting to see that some questions have more than 100 upvotes.
Posts.AnswerCount as [Number of Answers], -- I thought we shouldn't answer on off-topic post.
Posts.ViewCount,
Posts.FavoriteCount as [Number of Stars], -- Some questions seems to be very helpfull :) .
Posts.CreationDate as [Asked on], -- The older is the question, the more is the chance that flags on them can't get reviewed.
Posts.LastActivityDate as [last activity], -- Similar effect as with Posts.CreationDate.
Posts.LastEditDate as [modified on]
into #results
from Posts
INNER JOIN PendingFlags on PendingFlags.PostId = Posts.Id
LEFT OUTER JOIN Users on Users.id = posts.OwnerUserId
LEFT OUTER JOIN TopPerPost on Posts.id=TopPerPost.id
where seq=1
group by Posts.id, Posts.OwnerUserId, TopPerPost.Name, Reputation, Posts.Score, Posts.FavoriteCount, Posts.AnswerCount, Posts.CreationDate, Posts.LastActivityDate, Posts.LastEditDate, Posts.ViewCount
order by [Number of pending flags] desc, [The most common vote reason], Score desc, Reputation desc, FavoriteCount desc, ViewCount desc, Posts.CreationDate asc, LastActivityDate, LastEditDate -- Questions with more flags have more chance to get them handled, and the higher is the probabilty that the question is off-topic (since several users already reviewed the question).
select distinct [The most common vote reason] into #reasons from #results
ALTER TABLE #reasons
ADD id INT IDENTITY(1,1), html nvarchar(500)
create nonclustered index results_reasons_index
on #results ([The most common vote reason]);
create unique nonclustered index reasons_index
on #reasons ([The most common vote reason]);
declare @id int
declare @maxId as int
declare @markdown as nvarchar(500)
declare @html as nvarchar(500)
select @maxId = max(id) from #reasons
set @id = 0
while ( @id < @maxId )
begin
set @id = @id + 1
select @markdown = [The most common vote reason] from #reasons where id = @id
exec dbo.##HTMLtoMARKDOWN @text = @markdown, @returnText = @html output
update #reasons set html = @html where id = @id
end
update #results set [The most common vote reason] = #reasons.html
from #results
inner join #reasons
on #results.[The most common vote reason]
= #reasons.[The most common vote reason]
select * from #results
