2

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for professional mathematicians

--what % of my posts in the last two weeks were received positive/negative/neutral reaction?

select
  'site://q/'+cast(p.id as varchar)+'|'+case when p.posttypeid=1 then 'Q:'+p.title else 'A:'+q.title end as Link,
  format(p.creationdate,'dddd MMM d') as Posted,
  p.Score,
  p.commentcount as Comments,
  case when q.viewcount is null then p.viewcount else q.viewcount end as Views,
  case when q.acceptedanswerid = p.id then 'Yes' else 'No' end as Accepted,
  case when q.acceptedanswerid = p.id or p.score>0 then '+  positive' 
    else case when p.score<0 then '–  negative' else '•  neutral' end end as Reaction
into #temp from posts p left outer join posts q on p.parentid = q.id
where 
  p.owneruserid=##UserID##
  and p.creationdate >= dateadd(d,-14,(select min(create_date) from sys.tables))
  and p.posttypeid in (1,2)
order by p.creationdate desc

select 
sum(case when reaction like '%positive%' then 1 else 0 end) as Positive,
sum(case when reaction like '%neutral%' then 1 else 0 end) as Neutral,
sum(case when reaction like '%negative%' then 1 else 0 end) as Negative,
count(*) as total
into #temp2 from #temp

select 
  'Click "Results" tab (above) to view detail.' as [^^^^^^ . . . . . . . . . . . . . . . . . . . . . . .],
  total as [TOTAL POSTS],
  Positive,Negative,Neutral,
  format((1.0*Positive)/total,'0.0%') as [% Positive],
  format((1.0*(Positive+Neutral))/total,'0.0%') as [% Not-Negative]
from #temp2

select * from #temp

fork query

edited jul 19 18

created jul 19 18 ashleedawg