Dupehammered questions

0

Please login or register to vote for this query.

(click on this box to dismiss)

Windows Phone Meta

Q&A about the site for enthusiasts and power users of Windows Phone OS

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

Enter Parameters

Options:
Switch to main site
loading Hold tight while we fetch your results