Q&A about the site for Joomla! administrators, users, developers and designers
DECLARE @LastDay date = (SELECT CAST(MAX(CreationDate) AS date) FROM dbo.Posts WHERE PostTypeId = 1); DECLARE @WeekEnd date = CAST((CAST(@LastDay as datetime)) - 7 as date); DECLARE @WeekStart date = CAST((CAST(@WeekEnd as datetime)) - 28 as date); SELECT X.*, CAST(X.ClosedQs AS float) / CAST(X.AllQs AS float) * 100.0 AS CloseRate, CAST(X.ClosedAsDupeQs AS float) / CAST(X.ClosedQs as float) * 100.0 AS DupeCloseRate FROM ( SELECT CAST(P.CreationDate as date) AS OnDate, COUNT(*) AS AllQs, SUM( CASE WHEN P.ClosedDate IS NOT NULL THEN 1 ELSE 0 END ) AS ClosedQs, SUM ( (CASE WHEN Dup.Id IS NOT NULL THEN 1 ELSE 0 END) ) AS ClosedAsDupeQs FROM dbo.Posts P LEFT JOIN dbo.PostHistory Dup ON Dup.PostId = P.Id AND Dup.PostHistoryTypeId = 10 AND Dup.Comment = '1' WHERE P.PostTypeId = 1 AND P.CreationDate BETWEEN @WeekStart AND @WeekEnd GROUP BY CAST(P.CreationDate as date) ) X ORDER BY X.OnDate