0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for the craft of professional writing, including fiction, non-fiction, technical, scholarly, and commercial writing

SELECT
    DATEPART(year, rt.CreationDate) AS Year,
    DATEPART(month, rt.CreationDate) AS Month,
    DATEPART(day, rt.CreationDate) AS Day,
    COUNT(*) AS ReviewsRequested,
    COUNT(CASE WHEN rt.ReviewTaskTypeId = 1 THEN 1 ELSE NULL END) AS ReviewsReqTyp1,
    COUNT(CASE WHEN rt.ReviewTaskTypeId = 2 THEN 1 ELSE NULL END) AS ReviewsReqTyp2,
    COUNT(CASE WHEN rt.ReviewTaskTypeId = 3 THEN 1 ELSE NULL END) AS ReviewsReqTyp3,
    COUNT(CASE WHEN rt.ReviewTaskTypeId = 4 THEN 1 ELSE NULL END) AS ReviewsReqTyp4,
    COUNT(CASE WHEN rt.ReviewTaskTypeId = 5 THEN 1 ELSE NULL END) AS ReviewsReqTyp5,
    COUNT(CASE WHEN rt.ReviewTaskTypeId = 6 THEN 1 ELSE NULL END) AS ReviewsReqTyp6,
    COUNT(CASE WHEN rt.ReviewTaskTypeId = 7 THEN 1 ELSE NULL END) AS ReviewsReqTyp7,
    COUNT(CASE WHEN rt.ReviewTaskTypeId = 10 THEN 1 ELSE NULL END) AS ReviewsReqTyp10,
    COUNT(CASE WHEN rt.ReviewTaskTypeId = 11 THEN 1 ELSE NULL END) AS ReviewsReqTyp11,
    COUNT(CASE WHEN rt.ReviewTaskTypeId = 12 THEN 1 ELSE NULL END) AS ReviewsReqTyp12,
    COUNT(CASE WHEN rt.ReviewTaskTypeId = 13 THEN 1 ELSE NULL END) AS ReviewsReqTyp13,
    COUNT(CASE WHEN rt.ReviewTaskTypeId = 14 THEN 1 ELSE NULL END) AS ReviewsReqTyp14,
    COUNT(CASE WHEN rt.ReviewTaskStateId = 2 THEN 1 ELSE NULL END) AS ReviewsCompleted
FROM
    ReviewTasks rt
WHERE
    rt.CreationDate BETWEEN '2020-01-01' AND '2024-01-01'
GROUP BY
    DATEPART(year, rt.CreationDate),
    DATEPART(month, rt.CreationDate),
    DATEPART(day, rt.CreationDate)
ORDER BY
    Year, Month, Day;

view current version

edited apr 15

created apr 15 Jaeung Sim