Questions with close and deletion reasons

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

DECLARE @crt TABLE
(
  [reasonId] INT,
  [Id] INT,
  [Name] NVARCHAR(128)
)

INSERT @crt SELECT 1, 1,    'notUnique'
INSERT @crt SELECT 1, 101,  'notUnique'
INSERT @crt SELECT 2, 2,    'notOnTopic'
INSERT @crt SELECT 2, 102,  'notOnTopic'
INSERT @crt SELECT 3, 3,    'notConstructive'
INSERT @crt SELECT 4, 4,    'notQuestion'
INSERT @crt SELECT 5, 7,    'tooLocalized'
INSERT @crt SELECT 6, 10,   'generalReference'
INSERT @crt SELECT 7, 29,   'noiseOfPointless'
INSERT @crt SELECT 8, 103,  'notClear'
INSERT @crt SELECT 9, 104,  'notSpecific'
INSERT @crt SELECT 10, 105, 'notObjective'

SELECT
  [p].[Score],
  [ph].[Text] [ClosersJson],
  [p].[Title] [Post],
  [crt].[reasonId] [CloseReasonId],
  1 [Closed],
  [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
  [PostsWithDeleted] [p]
INNER JOIN [PostHistory] [ph] ON [p].[Id] = [ph].[PostId]
INNER JOIN [PostHistoryTypes] [pht] ON [ph].[PostHistoryTypeId] = [pht].[Id]
INNER JOIN @crt [crt] ON [ph].[Comment] = [crt].[Id]
WHERE 1 = 1
  AND [p].[PostTypeId] = 1
  AND [p].[ClosedDate] IS NOT NULL
  AND [ph].[PostHistoryTypeId] = 10
UNION
SELECT
  [p].[Score],
  NULL [ClosersJson],
  [p].[Title] [Post],
  11 [CloseReasonId],
  1 [Closed],
  'Deleted' [CloseReason],
  CONVERT(DATE, [p].[DeletionDate]) [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].[DeletionDate])) + '-' + CONVERT(NVARCHAR, DATEPART(MONTH, [p].[DeletionDate])) + '-01') [ClosedMonth],
  CASE WHEN DATEPART(WEEKDAY, [p].[CreationDate]) > 1 AND DATEPART(WEEKDAY, [p].[CreationDate]) < 7 THEN 1 ELSE 0 END [Weekday]
FROM
  [PostsWithDeleted] [p]
WHERE 1 = 1
  AND [p].[PostTypeId] = 1
  AND [p].[DeletionDate] IS NOT NULL
  AND [p].[ClosedDate] IS NULL
UNION
SELECT
  [p].[Score],
  NULL [ClosersJson],
  [p].[Title] [Post],
  NULL [CloseReasonId],
  0 [Closed],
  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
  [PostsWithDeleted] [p]
WHERE 1 = 1
  AND [p].[PostTypeId] = 1
  AND [p].[ClosedDate] IS NULL
  AND [p].[DeletionDate] IS NULL

Enter Parameters

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