SELECT Id, NULL ParentId, PostTypeId, ClosedDate, Deletio...

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

SELECT Id, NULL ParentId, PostTypeId, ClosedDate, DeletionDate
FROM PostsWithDeleted 
WHERE PostTypeId = 1
UNION ALL
SELECT a.Id, a.ParentId, a.PostTypeId, q.ClosedDate, COALESCE(a.DeletionDate, q.DeletionDate) DeletionDate
FROM PostsWithDeleted q INNER JOIN PostsWithDeleted a ON a.ParentId = q.Id

/*
SELECT
  'All',
  SUM(CASE PostTypeId WHEN 2 THEN 1 ELSE 0 END) Answers,
  SUM(CASE PostTypeId WHEN 1 THEN 1 ELSE 0 END) Questions
FROM PostsWithDeleted

UNION ALL SELECT
  'Open and not deleted',
  SUM(CASE PostTypeId WHEN 2 THEN 1 ELSE 0 END) Answers,
  SUM(CASE PostTypeId WHEN 1 THEN 1 ELSE 0 END) Questions
FROM PostsWithDeleted WHERE DeletionDate IS NULL AND ClosedDate IS NULL

UNION ALL SELECT
  'Not deleted',
  SUM(CASE PostTypeId WHEN 2 THEN 1 ELSE 0 END) Answers,
  SUM(CASE PostTypeId WHEN 1 THEN 1 ELSE 0 END) Questions
FROM PostsWithDeleted WHERE DeletionDate IS NULL
*/

Enter Parameters

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