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 10 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 AS TagName,
  ca4.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
  CROSS APPLY (
    SELECT Value FROM OPENJSON(
      (SELECT Value FROM OPENJSON(ca1.VALUE) WHERE [Key] = 'BindingReason')
    ) WHERE [Key] = 'GoldTagBadge'
  ) AS ca3
  CROSS APPLY (
    SELECT ca41.Value FROM OPENJSON(
      (SELECT Value FROM OPENJSON(h.Text) WHERE [Key] = 'Voters')
    ) AS ca40
    CROSS APPLY (
      SELECT Value FROM OPENJSON(ca40.Value) WHERE [Key] = 'DisplayName'
    ) AS ca41
    CROSS APPLY (
      SELECT Value FROM OPENJSON(ca40.Value) WHERE [Key] = 'Id'
    ) AS ca42
    WHERE ca42.Value != ca2.Value
  ) AS ca4
  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