0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for ebook publishers and readers

SELECT cast (year(p1.ClosedDate) as  varchar(10)) + '/' + cast(month(p1.ClosedDate) as varchar(19)) as ym
       ,  count(p1.Id) as ClosedQuestions
       , p2.pcount as TotalQuestions
       , count(p1.Id) / (p2.pcount*1.0) as Ratio
FROM PostHistory
JOIN Posts AS p1 ON p1.Id = PostHistory.PostId
JOIN 
(
SELECT cast (year(p.CreationDate) as  varchar(10)) + '/' + cast(month(p.CreationDate) as varchar(19)) as dt
, count(p.Id) as pcount
from Posts AS p
Where p.CreationDate > '2012-01-01' 
group by year(p.CreationDate), month(p.CreationDate)
) p2 ON p2.dt =  cast (year(p1.ClosedDate) as  varchar(10)) + '/' + cast(month(p1.ClosedDate) as varchar(19))
WHERE p1.ClosedDate >= '2012-01-01' 
AND p1.ClosedDate < '2013-02-01'
AND PostHistoryTypeId = 10
AND PostHistory.Comment != 1
group by year(p1.ClosedDate), month(p1.ClosedDate), p2.pcount
ORDER BY 1 desc, 2 desc

fork query

edited feb 8 13 Matt Ellen

created nov 16 12 hairboat