DECLARE @YouId int = ##UserId:int?3684##; -- Erwin ...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for programmers

-- Enter UserId of match partners.

DECLARE @YouId int = ##UserId:int?3684##;      -- Erwin as default
DECLARE @HeId  int = ##OtherUserId:int?22656##;  -- Jon as default

WITH you AS(                -- Your answer scores summed up per question
    SELECT a.ParentId, q.AcceptedAnswerId
         , sum(a.Score) AS youScore
         , sum(CASE WHEN q.AcceptedAnswerId = a.Id THEN 1 ELSE 0 END) AS youAccept
    FROM   Posts a
    JOIN   Posts q ON q.id = a.ParentId
    WHERE  a.OwnerUserId = @YouId
    AND    a.postTypeId  = 2
    AND    EXISTS (        -- only where he answered as well
       SELECT 1 FROM Posts ah
       WHERE  ah.OwnerUserId = @HeId
       AND    ah.postTypeId  = 2
       AND    ah.ParentId = a.ParentId
       )
    GROUP  BY a.ParentId, q.AcceptedAnswerId
    )
   , he AS (               -- His answer scores summed up per question
    SELECT a.ParentId
         , a.CreationDate
         , y.youScore
         , y.youAccept
         , sum(a.Score) AS heScore
         , sum(CASE WHEN y.AcceptedAnswerId = a.Id THEN 1 ELSE 0 END) AS heAccept
    FROM   you y            -- only where you answered, too
    JOIN   Posts a ON a.ParentId = y.ParentId
    WHERE  a.OwnerUserId = @HeId
    AND    a.postTypeId  = 2
    GROUP  BY a.ParentId, a.CreationDate, y.youScore, y.youAccept
    )
SELECT ParentId AS [Post Link]
     , youScore AS [Your Upvotes]
     , heScore  AS [His Upvotes]
     , CASE
          WHEN youScore > heScore THEN 'YOU'
          WHEN youScore < heScore THEN 'he'
       END AS [Upvote Winner]
     , CASE 
          WHEN youAccept = 1 THEN 'YOU'
          WHEN heAccept  = 1 THEN 'he'
       END AS [Accepted Answer]
     , CONVERT(varchar(10), CreationDate, 120) AS CreationDate
FROM   he

UNION  ALL
SELECT NULL           -- Post Link
     , sum(youScore)  -- Your Upvotes
     , sum(heScore)   -- His  Upvotes
     , CAST(sum(CASE WHEN youScore > heScore THEN 1 ELSE 0 END) AS varchar)
       + ' : '
       + CAST(sum(CASE WHEN youScore < heScore THEN 1 ELSE 0 END) AS varchar) -- UpvoteWinner
     , CAST(sum(youAccept) AS varchar)
       + ' : '
       + CAST(sum(heAccept) AS varchar)  -- Accepted Answer
     , NULL           -- CreationDate
FROM   he
ORDER  BY ParentId;

Enter Parameters

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