DECLARE @LastDate datetime SELECT @LastDate = MAX(Creatio...

0

Please login or register to vote for this query.

(click on this box to dismiss)

DevOps

Q&A for software engineers working on automated testing, continuous delivery, service integration and monitoring, and building SDLC infrastructure

DECLARE @LastDate datetime
SELECT @LastDate = MAX(CreationDate) FROM PostHistory;

WITH Threshold AS (
  SELECT TOP 60 ROW_NUMBER() OVER (ORDER BY Id) Days FROM PostHistory
)

SELECT
  Days,
  ##Area51RefValue?3.4## Area51Ref,
  1.0 * AllQuestions / Days [All],
  1.0 * NondeletedQuestions / Days Nondeleted,
  1.0 * OpenNondeletedQuestions / Days OpenNondeleted
FROM (
  SELECT
   Days,
   (SELECT COUNT(*) FROM PostsWithDeleted p
    WHERE DATEDIFF(DAY, p.CreationDate, @LastDate) < Days
    AND p.PostTypeId = 1) AllQuestions,
   (SELECT COUNT(*) FROM PostsWithDeleted p
    WHERE DATEDIFF(DAY, p.CreationDate, @LastDate) < Days
    AND p.PostTypeId = 1 AND DeletionDate IS NULL) NondeletedQuestions,
   (SELECT COUNT(*) FROM PostsWithDeleted p
    WHERE DATEDIFF(DAY, p.CreationDate, @LastDate) < Days
    AND p.PostTypeId = 1 AND DeletionDate IS NULL AND ClosedDate IS NULL) OpenNondeletedQuestions
  FROM Threshold
) x

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results