0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for gamemasters and players of tabletop, paper-and-pencil role-playing games

SELECT TOP 100 p.Id AS [Post Link], p.Tags,
  h.CreationDate AS 'Close date',
  CAST(ca0.Value AS INT) AS [Post Link],
  CAST(ca2.Value AS INT) AS [User Link], ca3.Value
  FROM PostHistory AS h
  INNER JOIN Posts AS p ON h.PostId = p.Id
  CROSS APPLY (
    SELECT TOP 1 Value FROM OPENJSON(
      (SELECT Value FROM OPENJSON(h.Text) WHERE [Key] = 'OriginalQuestionIds')
    )
  ) AS ca0
  CROSS APPLY (
    SELECT Value FROM OPENJSON(
      (SELECT Value FROM OPENJSON(h.Text) WHERE [Key] = 'Voters')
    )
  ) AS ca1
  CROSS APPLY (
    SELECT Value FROM OPENJSON(ca1.VALUE) WHERE [Key] = 'Id'
  ) AS ca2
  OUTER APPLY (
    SELECT Value FROM OPENJSON(ca1.VALUE) WHERE [Key] = 'BindingReason'
  ) AS ca3
  WHERE h.PostHistoryTypeId = 10 -- Close
    AND h.Comment = 101 -- Exact Duplicate
  ORDER BY h.CreationDate DESC

view current version

edited jul 18 19

created jul 18 19 Glorfindel