WITH GeneralStats AS ( SELECT COUNT(*) Total, SUM(IIF(p.C...

0

Please login or register to vote for this query.

(click on this box to dismiss)

TeX - LaTeX

Q&A for users of TeX, LaTeX, ConTeXt, and related typesetting systems

WITH GeneralStats AS (
  SELECT
    COUNT(*) Total,
    SUM(IIF(p.ClosedDate IS NULL AND p.DeletionDate IS NULL, 1, 0)) [Open],
    SUM(IIF(p.ClosedDate IS NOT NULL AND p.DeletionDate IS NULL, 1, 0)) Closed,
    COUNT(p.DeletionDate) Deleted
  FROM
    PostsWithDeleted p
  WHERE
    p.PostTypeId = 1 
    AND DATEDIFF(DAY, p.CreationDate, GETDATE()) < 365
),

ReopenStats AS (
  SELECT
    SUM(IIF(Comment IN (103, 104), 1, 0)) ClosedUnclearBroad,
    SUM(IIF(ClosedDate IS NULL AND Comment IN (103, 104), 1, 0)) ReopenedUnclearBroad,
    SUM(IIF(ClosedDate IS NULL, 1, 0)) Reopened
  FROM (
    SELECT DISTINCT
      p.Id, p.ClosedDate, h.Comment
    FROM
      Posts p
      INNER JOIN PostHistory h ON h.PostId = p.Id
    WHERE
      p.PostTypeId = 1
      --AND p.ClosedDate IS NULL
      AND h.PostHistoryTypeId = 10
      AND DATEDIFF(DAY, p.CreationDate, GETDATE()) < 365
    ) ids
),
  
MeanStats AS (
  SELECT
    AVG(DATEDIFF(MINUTE, p.CreationDate, p.ClosedDate)) AvgCloseMinutes,
    MIN(DATEDIFF(MINUTE, p.CreationDate, p.ClosedDate)) MinCloseMinutes,
    MAX(DATEDIFF(MINUTE, p.CreationDate, p.ClosedDate)) MaxCloseMinutes
  FROM
    Posts p
  WHERE
    p.PostTypeId = 1 
    AND p.ClosedDate IS NOT NULL 
    AND DATEDIFF(DAY, p.CreationDate, GETDATE()) < 365
),

MedianStats AS (
  SELECT
    AVG(CloseTime) MedCloseMinutes
  FROM
    (SELECT 
       DATEDIFF(MINUTE, p.CreationDate, p.ClosedDate) CloseTime,
       ROW_NUMBER() OVER (ORDER BY DATEDIFF(MINUTE, p.CreationDate, p.ClosedDate)) A,
       ROW_NUMBER() OVER (ORDER BY DATEDIFF(MINUTE, p.CreationDate, p.ClosedDate) DESC) D
     FROM 
       Posts p
     WHERE
       p.PostTypeId = 1 
       AND p.ClosedDate IS NOT NULL 
       AND DATEDIFF(DAY, p.CreationDate, GETDATE()) < 365
     ) times
  WHERE
    A BETWEEN D - 1 AND D + 1
)

SELECT
  *
FROM
  GeneralStats, ReopenStats, MeanStats, MedianStats

Enter Parameters

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