Q&A about the site for mechanics and DIY enthusiast owners of cars, trucks, and motorcycles
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 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 Posts.ClosedDate < '2012-12-01' AND PostHistoryTypeId = 10 AND PostHistory.Comment != 1 group by year(p1.ClosedDate), month(p1.ClosedDate), p2.pcount ORDER BY 1 desc, 2 desc