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 p.Id AS 'Post Link', p.Tags,
  h.CreationDate AS 'Close date',
  CAST(ca0.Value AS INT) AS 'Post Link', (SELECT Tags FROM Posts WHERE Id = CAST(ca0.Value AS INT)) AS Tags,
  CAST(ca2.Value AS INT) AS 'User Link',
  ca3.Value AS TagName,
  ca4.[Count] AS '#OCV', ca4.Value AS 'Other close voters'
  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 COUNT(*) AS [Count], STRING_AGG(ca41.Value, ', ') AS 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
    AND h.CreationDate >= ##fromDate:string?2020-01-01##
    AND h.CreationDate <= ##toDate:string?2020-12-31##
  ORDER BY h.CreationDate DESC

fork query

edited jan 21 21 SomeoneEvil

created jul 18 19 Glorfindel