0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

DECLARE @Question tinyint = 1;
DECLARE @Answer tinyint = 2;
DECLARE @CloseVoteReview tinyint = 2;
DECLARE @Invalid tinyint = 3;

With CloseReview AS (
  SELECT
    CASE
      WHEN State.Name = 'Completed' THEN 'Completed'
      WHEN (
        (
          DATEDIFF(d, r.CreationDate, r.DeletionDate) >= 14 AND
          DATEDIFF(d, r.CreationDate, r.DeletionDate) <= 17
        )  OR  (
          DATEDIFF(d, r.CreationDate, r.DeletionDate) >=4 AND
          DATEDIFF(d, r.CreationDate, r.DeletionDate) <=7
        )
      ) THEN 'Invalidated - Presumed Expired'
      ELSE 'Invalidated - Other (May be Expired)'
    END as StateBin
  FROM ReviewTasks r
  JOIN ReviewTaskStates state ON r.ReviewTaskStateId = state.Id 
  WHERE
    r.ReviewTaskTypeId = @CloseVoteReview
  AND
    YEAR(r.DeletionDate) = 2022
    AND MONTH(r.DeletionDate) = 01
),

CloseReviewSummary AS (
  SELECT StateBin,
    Count(*) as StateTotal
  FROM CloseReview
  GROUP BY StateBin
)

SELECT StateBin,
  StateTotal,
  FORMAT(((1.00 * StateTotal)/(SELECT sum(StateTotal) FROM CloseReviewSummary)), 'P2') As "Percent"
FROM CloseReviewSummary
ORDER BY [Percent] DESC

fork query

edited jan 24

created jan 23 NotTheDr01ds