Close reasons over time for further analysis

0

Please login or register to vote for this query.

(click on this box to dismiss)

Software Engineering

Q&A for professionals, academics, and students working within the systems development life cycle

SELECT [ClosedMonth], [CloseReason], COUNT(1) [Closed]
FROM
(
SELECT
  [p].[Score],
  [ph].[Text] [ClosersJson],
  [p].[Title] [Post],
  [crt].[Name] [CloseReason],
  CONVERT(DATE, [p].[ClosedDate]) [ClosedDate],
  CONVERT(DATE, [p].[CreationDate]) [CreationDate],
  CONVERT(DATE, CONVERT(NVARCHAR, DATEPART(YEAR, [p].[CreationDate])) + '-' + CONVERT(NVARCHAR, DATEPART(MONTH, [p].[CreationDate])) + '-01') [CreationMonth],
  CONVERT(DATE, CONVERT(NVARCHAR, DATEPART(YEAR, [p].[ClosedDate])) + '-' + CONVERT(NVARCHAR, DATEPART(MONTH, [p].[ClosedDate])) + '-01') [ClosedMonth],
  CASE WHEN DATEPART(WEEKDAY, [p].[CreationDate]) > 1 AND DATEPART(WEEKDAY, [p].[CreationDate]) < 7 THEN 1 ELSE 0 END [Weekday]
FROM
  [Posts] [p]
INNER JOIN [PostHistory] [ph] ON [p].[Id] = [ph].[PostId]
INNER JOIN [PostHistoryTypes] [pht] ON [ph].[PostHistoryTypeId] = [pht].[Id]
INNER JOIN [CloseReasonTypes] [crt] ON [ph].[Comment] = [crt].[Id]
WHERE 1 = 1
  AND [p].[PostTypeId] = 1
  AND [ph].[PostHistoryTypeId] = 10
UNION
SELECT
  [p].[Score],
  NULL [ClosersJson],
  [p].[Title] [Post],
  NULL [CloseReason],
  NULL [ClosedDate],
  CONVERT(DATE, [p].[CreationDate]) [CreationDate],
  CONVERT(DATE, CONVERT(NVARCHAR, DATEPART(YEAR, [p].[CreationDate])) + '-' + CONVERT(NVARCHAR, DATEPART(MONTH, [p].[CreationDate])) + '-01') [CreationMonth],
  NULL [ClosedMonth],
  CASE WHEN DATEPART(WEEKDAY, [p].[CreationDate]) > 1 AND DATEPART(WEEKDAY, [p].[CreationDate]) < 7 THEN 1 ELSE 0 END [Weekday]
FROM
  [Posts] [p]
WHERE 1 = 1
  AND [p].[PostTypeId] = 1
  AND [p].[ClosedDate] IS NULL
  AND [p].[DeletionDate] IS NULL
) [d] WHERE [d].[ClosedMonth] IS NOT NULL
GROUP BY [ClosedMonth], [CloseReason]

Enter Parameters

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