;with before as ( select count(*) [bcnt] from posthist...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Earth Science

Q&A for those interested in the geology, meteorology, oceanography, and environmental sciences

--select *
--from closeasofftopicreasontypes

-- 2018-12-03 05:59:59

-- select * from closereasontypes
-- and t.tagname = 'identification-request'

;with before as (
select count(*) [bcnt]
from posthistory ph 
where posthistorytypeid = 10
and comment in ('2', '102')
and ph.creationdate < '2018-12-03 05:59:59'
)

select datediff(d, p.creationdate, ph.creationdate) [days it took to close]
     , count(*) [# questions closed]
     , (100* convert(decimal,count(*)) /
     ((select bcnt from before))) [% of total]
from posthistory ph 
inner join posts p on p.id = ph.postid
where posthistorytypeid = 10
and comment in ('2', '102')
and ph.creationdate < '2018-12-03 05:59:59'
group by datediff(d, p.creationdate, ph.creationdate) 

;with after as (
select count(*) [acnt]
from posthistory ph 
where posthistorytypeid = 10
and comment in ('2', '102')
and ph.creationdate >= '2018-12-03 05:59:59'
)

select datediff(d, p.creationdate, ph.creationdate) [days it took to close]
     , count(*) [# questions closed]
     , (100* convert(decimal,count(*)) /
     ((select acnt from after))) [% of total]
from posthistory ph 
inner join posts p on p.id = ph.postid
where posthistorytypeid = 10
and comment in ('2', '102')
and ph.creationdate > '2018-12-03 05:59:59'
group by datediff(d, p.creationdate, ph.creationdate) 


/*
select ph.postid as [Post Link]
     , comment
     , creationdate
     , t.tagname
from posthistory ph 
inner join posttags pt on pt.postid = ph.postid
inner join tags t on t.id = pt.tagid
where posthistorytypeid = 10
and comment in ('2', '102')
and creationdate > '2018-12-03 05:59:59'
and t.tagname = 'identification-request'
*/

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results