0

Please login or register to vote for this query.

(click on this box to dismiss)

2 tabs. Thanks Erik for updating :)

Q&A about the site for professional robotic engineers, hobbyists, researchers and students

--using postswithdeleted

select  --MONTHLY (Tab#1)
  eomonth(closeddate) as ClosedMonth,
  format(count(*),'#,##0') as [#ClosedQuestions],
  format(1.0*sum(Score)/count(*),'0.0') as [Avg Score],
  format(sum(case when not lasteditdate is null then 1 else 0 end),'#,##0') as [#Edited],
  format(sum(case when lasteditdate>closeddate AND LastEditorUserID <> -1 
        then 1 else 0 end),'#,##0') as [#Edited After Close],
  format(sum(case when lasteditdate>closeddate AND LastEditorUserID <> -1 then 1 else 0 end) 
            / count(id),'0.0%') as [%Edit After Close]
from postswithdeleted
where closeddate is not null and posttypeid=1
group by eomonth(closeddate)
order by eomonth(closeddate)

select  --SUMMARY (Tab#2)
  format(count(*),'#,##0') as [#ClosedQuestions],
  format(1.0*sum(Score)/count(*),'0.0') as [Avg Score],
  format(sum(case when not lasteditdate is null then 1 else 0 end),'#,##0') as [#Edited],
  format(sum(case when lasteditdate>closeddate AND LastEditorUserID <> -1 
        then 1 else 0 end),'#,##0') as [#Edited After Close],
  format(sum(case when lasteditdate>closeddate AND LastEditorUserID <> -1 then 1 else 0 end) 
            / count(id),'0.0%') as [%Edit After Close]
from postswithdeleted
where closeddate is not null and posttypeid=1

fork query

edited aug 5 18

created aug 2 18 ashleedawg