1

Please login or register to vote for this query.

(click on this box to dismiss)

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

fork query

edited jul 10 12

created mar 29 12 anonymous