Have they met?

Please login or register to vote for this query.

(click on this box to dismiss)
Where have I 'met' another user? Checks for questions, answers, comments and bounties. Note: detecting comment replies rely on text matching and can include false positives.
Q&A for cartographers, geographers and GIS professionals
-- Have they met?
-- Where have I 'met' another user? Checks for questions, answers, comments and bounties. 
-- Note: detecting comment replies rely on text matching and can include false positives.

declare @AId int = ##UserAId##;
declare @BId int = ##UserBId##;

-- Translation of the inverse of the \w'.- pattern from
-- https://meta.stackexchange.com/a/51913 to the codepoints and ranges in the
-- BMP that *don't* match. Pattern loaded via JSON literal to avoid headaches
-- with nvarchar literals entered via a web interface.
declare @ToRemove as nvarchar(1200) = JSON_Value(
    N'{"p": "%[\u0000-&(-,/:-@[-^`{-\u00a9\u00ab-\u00b4\u00b6-\u00b9\u00bb-\u00bf\u00d7\u00f7\u02c2-\u02c5\u02d2-\u02df\u02e5-\u02eb\u02ed\u02ef-\u02ff\u0375\u0378-\u0379\u037e\u0380-\u0385\u0387\u038b\u038d\u03a2\u03f6\u0482\u0488-\u0489\u0530\u0557-\u0558\u055a-\u055f\u0589-\u0590\u05be\u05c0\u05c3\u05c6\u05c8-\u05cf\u05eb-\u05ee\u05f3-\u060f\u061b-\u061f\u066a-\u066d\u06d4\u06dd-\u06de\u06e9\u06fd-\u06fe\u0700-\u070f\u074b-\u074c\u07b2-\u07bf\u07f6-\u07f9\u07fb-\u07fc\u07fe-\u07ff\u082e-\u083f\u085c-\u085f\u086b-\u089f\u08b5\u08be-\u08d2\u08e2\u0903\u093b\u093e-\u0940\u0949-\u094c\u094e-\u094f\u0964-\u0965\u0970\u0982-\u0984\u098d-\u098e\u0991-\u0992\u09a9\u09b1\u09b3-\u09b5\u09ba-\u09bb\u09be-\u09c0\u09c5-\u09cc\u09cf-\u09db\u09de\u09e4-\u09e5\u09f2-\u09fb\u09fd\u09ff-\u0a00\u0a03-\u0a04\u0a0b-\u0a0e\u0a11-\u0a12\u0a29\u0a31\u0a34\u0a37\u0a3a-\u0a3b\u0a3d-\u0a40\u0a43-\u0a46\u0a49-\u0a4a\u0a4e-\u0a50\u0a52-\u0a58\u0a5d\u0a5f-\u0a65\u0a76-\u0a80\u0a83-\u0a84\u0a8e\u0a92\u0aa9\u0ab1\u0ab4\u0aba-\u0abb\u0abe-\u0ac0\u0ac6\u0ac9-\u0acc\u0ace-\u0acf\u0ad1-\u0adf\u0ae4-\u0ae5\u0af0-\u0af8\u0b00\u0b02-\u0b04\u0b0d-\u0b0e\u0b11-\u0b12\u0b29\u0b31\u0b34\u0b3a-\u0b3b\u0b3e\u0b40\u0b45-\u0b4c\u0b4e-\u0b55\u0b57-\u0b5b\u0b5e\u0b64-\u0b65\u0b70\u0b72-\u0b81\u0b84\u0b8b-\u0b8d\u0b91\u0b96-\u0b98\u0b9b\u0b9d\u0ba0-\u0ba2\u0ba5-\u0ba7\u0bab-\u0bad\u0bba-\u0bbf\u0bc1-\u0bcc\u0bce-\u0bcf\u0bd1-\u0be5\u0bf0-\u0bff\u0c01-\u0c03\u0c0d\u0c11\u0c29\u0c3a-\u0c3c\u0c41-\u0c45\u0c49\u0c4e-\u0c54\u0c57\u0c5b-\u0c5f\u0c64-\u0c65\u0c70-\u0c7f\u0c82-\u0c84\u0c8d\u0c91\u0ca9\u0cb4\u0cba-\u0cbb\u0cbe\u0cc0-\u0cc5\u0cc7-\u0ccb\u0cce-\u0cdd\u0cdf\u0ce4-\u0ce5\u0cf0\u0cf3-\u0cff\u0d02-\u0d04\u0d0d\u0d11\u0d3e-\u0d40\u0d45-\u0d4c\u0d4f-\u0d53\u0d57-\u0d5e\u0d64-\u0d65\u0d70-\u0d79\u0d80-\u0d84\u0d97-\u0d99\u0db2\u0dbc\u0dbe-\u0dbf\u0dc7-\u0dc9\u0dcb-\u0dd1\u0dd5\u0dd7-\u0de5\u0df0-\u0e00\u0e3b-\u0e3f\u0e4f\u0e5a-\u0e80\u0e83\u0e85\u0e8b\u0ea4\u0ea6\u0ebe-\u0ebf\u0ec5\u0ec7\u0ece-\u0ecf\u0eda-\u0edb\u0ee0-\u0eff\u0f01-\u0f17\u0f1a-\u0f1f\u0f2a-\u0f34\u0f36\u0f38\u0f3a-\u0f3f\u0f48\u0f6d-\u0f70\u0f7f\u0f85\u0f98\u0fbd-\u0fc5\u0fc7-\u0fff\u102b-\u102c\u1031\u1038\u103b-\u103c\u104a-\u104f\u1056-\u1057\u1062-\u1064\u1067-\u106d\u1083-\u1084\u1087-\u108c\u108f\u109a-\u109c\u109e-\u109f\u10c6\u10c8-\u10cc\u10ce-\u10cf\u10fb\u1249\u124e-\u124f\u1257\u1259\u125e-\u125f\u1289\u128e-\u128f\u12b1\u12b6-\u12b7\u12bf\u12c1\u12c6-\u12c7\u12d7\u1311\u1316-\u1317\u135b-\u135c\u1360-\u137f\u1390-\u139f\u13f6-\u13f7\u13fe-\u1400\u166d-\u166e\u1680\u169b-\u169f\u16eb-\u16f0\u16f9-\u16ff\u170d\u1715-\u171f\u1735-\u173f\u1754-\u175f\u176d\u1771\u1774-\u177f\u17b6\u17be-\u17c5\u17c7-\u17c8\u17d4-\u17d6\u17d8-\u17db\u17de-\u17df\u17ea-\u180a\u180e-\u180f\u181a-\u181f\u1879-\u187f\u18ab-\u18af\u18f6-\u18ff\u191f\u1923-\u1926\u1929-\u1931\u1933-\u1938\u193c-\u1945\u196e-\u196f\u1975-\u197f\u19ac-\u19af\u19ca-\u19cf\u19da-\u19ff\u1a19-\u1a1a\u1a1c-\u1a1f\u1a55\u1a57\u1a5f\u1a61\u1a63-\u1a64\u1a6d-\u1a72\u1a7d-\u1a7e\u1a8a-\u1a8f\u1a9a-\u1aa6\u1aa8-\u1aaf\u1abe-\u1aff\u1b04\u1b35\u1b3b\u1b3d-\u1b41\u1b43-\u1b44\u1b4c-\u1b4f\u1b5a-\u1b6a\u1b74-\u1b7f\u1b82\u1ba1\u1ba6-\u1ba7\u1baa\u1be7\u1bea-\u1bec\u1bee\u1bf2-\u1bff\u1c24-\u1c2b\u1c34-\u1c35\u1c38-\u1c3f\u1c4a-\u1c4c\u1c7e-\u1c7f\u1c89-\u1c8f\u1cbb-\u1cbc\u1cc0-\u1ccf\u1cd3\u1ce1\u1cf7\u1cfb-\u1cff\u1dfa\u1f16-\u1f17\u1f1e-\u1f1f\u1f46-\u1f47\u1f4e-\u1f4f\u1f58\u1f5a\u1f5c\u1f5e\u1f7e-\u1f7f\u1fb5\u1fbd\u1fbf-\u1fc1\u1fc5\u1fcd-\u1fcf\u1fd4-\u1fd5\u1fdc-\u1fdf\u1fed-\u1ff1\u1ff5\u1ffd-\u203e\u2041-\u2053\u2055-\u2070\u2072-\u207e\u2080-\u208f\u209d-\u20cf\u20dd-\u20e0\u20e2-\u20e4\u20f1-\u2101\u2103-\u2106\u2108-\u2109\u2114\u2116-\u2118\u211e-\u2123\u2125\u2127\u2129\u212e\u213a-\u213b\u2140-\u2144\u214a-\u214d\u214f-\u215f\u2170-\u2182\u2185-\u24b5\u24d0-\u2bff\u2c2f\u2c5f\u2ce5-\u2cea\u2cf4-\u2cff\u2d26\u2d28-\u2d2c\u2d2e-\u2d2f\u2d68-\u2d6e\u2d70-\u2d7e\u2d97-\u2d9f\u2da7\u2daf\u2db7\u2dbf\u2dc7\u2dcf\u2dd7\u2ddf\u2e00-\u2e2e\u2e30-\u3004\u3007-\u3029\u302e-\u3030\u3036-\u303a\u303d-\u3040\u3097-\u3098\u309b-\u309c\u30a0\u30fb\u3100-\u3104\u3130\u318f-\u319f\u31bb-\u31ef\u3200-\u33ff\u4db6-\u4dff\u9ff0-\u9fff\ua48d-\ua4cf\ua4fe-\ua4ff\ua60d-\ua60f\ua62c-\ua63f\ua670-\ua673\ua67e\ua6e6-\ua6ef\ua6f2-\ua716\ua720-\ua721\ua789-\ua78a\ua7c0-\ua7c1\ua7c7-\ua7f6\ua823-\ua824\ua827-\ua83f\ua874-\ua881\ua8b4-\ua8c3\ua8c6-\ua8cf\ua8da-\ua8df\ua8f8-\ua8fa\ua8fc\ua92e-\ua92f\ua952-\ua95f\ua97d-\ua97f\ua983\ua9b4-\ua9b5\ua9ba-\ua9bb\ua9be-\ua9ce\ua9da-\ua9df\ua9ff\uaa2f-\uaa30\uaa33-\uaa34\uaa37-\uaa3f\uaa4d-\uaa4f\uaa5a-\uaa5f\uaa77-\uaa79\uaa7b\uaa7d\uaac3-\uaada\uaade-\uaadf\uaaeb\uaaee-\uaaf1\uaaf5\uaaf7-\uab00\uab07-\uab08\uab0f-\uab10\uab17-\uab1f\uab27\uab2f\uab5b\uab68-\uab6f\uabe3-\uabe4\uabe6-\uabe7\uabe9-\uabec\uabee-\uabef\uabfa-\uabff\ud7a4-\ud7af\ud7c7-\ud7ca\ud7fc-\uf8ff\ufa6e-\ufa6f\ufada-\ufaff\ufb07-\ufb12\ufb18-\ufb1c\ufb29\ufb37\ufb3d\ufb3f\ufb42\ufb45\ufbb2-\ufbd2\ufd3e-\ufd4f\ufd90-\ufd91\ufdc8-\ufdef\ufdfc-\ufdff\ufe10-\ufe1f\ufe30-\ufe32\ufe35-\ufe4c\ufe50-\ufe6f\ufe75\ufefd-\uff0f\uff1a-\uff20\uff3b-\uff3e\uff40\uff5b-\uff65\uffbf-\uffc1\uffc8-\uffc9\uffd0-\uffd1\uffd8-\uffd9\uffdd-\uffff]%"}',
    '$.p'
);
declare @Temp nvarchar(40);
declare @MatchPos int;

declare @AName nvarchar(40);
declare @BName nvarchar(40);
select @AName = DisplayName from Users where Id = @AId;
select @BName = DisplayName from Users where Id = @BId

set @Temp = Lower(@AName);
while (1=1)
begin
  set @MatchPos = PatIndex(@ToRemove, @Temp COLLATE Latin1_General_BIN2)
  if(@MatchPos > 0)
    set @Temp = Replace(@Temp, Substring(@Temp, @MatchPos, 1), '')
  else
    break
end
declare @LikeAName nvarchar(40) = FORMATMESSAGE('%%@%s%%', @Temp);

set @Temp = Lower(@BName);
while (1=1)
begin
  set @MatchPos = PatIndex(@ToRemove, @Temp COLLATE Latin1_General_BIN2)
  if(@MatchPos > 0)
    set @Temp = Replace(@Temp, Substring(@Temp, @MatchPos, 1), '')
  else
    break
end
declare @LikeBName nvarchar(40) = FORMATMESSAGE('%%@%s%%', @Temp)

-- Answers to questions
  select
    FORMATMESSAGE('site://q/%d|%s', Questions.Id, Questions.Title) as Link,  
    case
      when Questions.OwnerUserId = @AId then
        FORMATMESSAGE('%s''s question, %s''s answer', @AName, @BName)
      else
        FORMATMESSAGE('%s''s question, %s''s answer', @BName, @AName)
    end as [What],
    case
      when Questions.OwnerUserId = @AId then
        Questions.CreationDate
      else
        Answers.CreationDate
    end as [Date A],
    case
      when Answers.OwnerUserId = @AId then
        Questions.CreationDate
      else
        Answers.CreationDate
    end as [Date B]
  from Posts as Questions
  inner join Posts as Answers
    on Questions.Id = Answers.ParentId
  where Answers.PostTypeId = 2 and Questions.PostTypeId = 1
  and (
        (Questions.OwnerUserId = @AId and Answers.OwnerUserId = @BId)
     or (Questions.OwnerUserid = @BId and Answers.OwnerUserId = @AId)
  )
  
union
  
-- Comments on posts
  select 
    FORMATMESSAGE('site://posts/comments/%d|%s', Comment.Id, Coalesce(Posts.Title, Question.Title)) as Link,  
    case
      when Posts.PostTypeId = 1 then
      	case
      		when Posts.OwnerUserId = @AId then
	        	FORMATMESSAGE('%s''s question, %s''s comment', @AName, @BName)
  	    	else
    	  		FORMATMESSAGE('%s''s question, %s''s comment', @BName, @AName)
    	  end
    	else
    		case
    			when Posts.OwnerUserId = @AId and Comment.UserId = @BId then
        		FORMATMESSAGE('%s''s answer, %s''s comment', @AName, @BName)
    			when Posts.OwnerUserId = @BId and Comment.UserId = @AId then
        		FORMATMESSAGE('%s''s answer, %s''s comment', @BName, @AName)
    			when Question.OwnerUserId = @AId and Comment.UserId = @BId then
        		FORMATMESSAGE('%s''s question, %s''s comment to one of the answers', @AName, @BName)
    			when Question.OwnerUserId = @BId and Comment.UserId = @AId then
        		FORMATMESSAGE('%s''s question, %s''s comment to one of the answers', @BName, @AName)
        end
    end as [What],
    case
      when Posts.OwnerUserId = @AId then
        Posts.CreationDate
      when Comment.UserId = @AId then
        Comment.CreationDate
      when Question.OwnerUserid = @AId then
      	Question.CreationDate
    end as [Date A],
    case
      when Posts.OwnerUserId = @BId then
        Posts.CreationDate
      when Comment.UserId = @BId then
        Comment.CreationDate
      when Question.OwnerUserid = @BId then
      	Question.CreationDate
    end as [Date B]
  from Posts
  inner join Comments as Comment
    on Posts.Id = Comment.PostId
  left outer join Posts as Question
    on Posts.parentid = Question.id
  where 
      Posts.PostTypeId in (1, 2)
  and (
        (Posts.OwnerUserId = @AId and Comment.UserId = @BId)
     or (Posts.OwnerUserid = @BId and Comment.UserId = @AId)
     or (Posts.PostTypeId = 2 and Question.OwnerUserId = @AId and Comment.UserId = @BId)
     or (Posts.PostTypeId = 2 and Question.OwnerUserId = @BId and Comment.UserId = @AId)
  )

union

-- Reply comments
  select
  FORMATMESSAGE('site://posts/comments/%d|%s', Comments.Id, COALESCE(Posts.Title, Question.Title)),
    case
      when Comments.UserId = @AId then
        FORMATMESSAGE('%s''s reply to %s''s comment', @AName, @BName)
      when Comments.UserId = @BId then
        FORMATMESSAGE('%s''s reply to %s''s comment', @BName, @AName)
    end as [What],
    -- can't know the date of the comment they addressed
    -- so the following are the date of the post the comment is attached to
    -- and the date of the replying comment
    case
      when Comments.UserId = @AId then
        Comments.CreationDate
      else
        Posts.CreationDate
    end as [Date A],
    case
      when Comments.UserId = @AId then
        Posts.CreationDate
      else
        Comments.CreationDate
    end as [Date B]
  from Comments
  inner join Posts on Comments.PostId = Posts.Id
  left outer join Posts as Question on Posts.parentid = Question.id  where (
       (UserId = @AId and LOWER(Text) like @LikeBName)
    or (UserId = @BId and LOWER(Text) like @LikeAName)
  )
  
union

 -- Both answered the same question
  select
    FORMATMESSAGE('site://q/%d|%s', Question.Id, Question.Title) as Link,  
    'Both answered the same question' as [What],
    Answer1.CreationDate as [Date A],
    Answer2.CreationDate as [Date B]
  from Posts as Question
  inner join Posts as Answer1
   on Question.Id = Answer1.ParentId
  inner join Posts as Answer2
   on Question.Id = Answer2.ParentId
  where
       Question.OwnerUserId NOT IN (@AId, @BId)
   and Answer1.OwnerUserId = @AId
   and Answer2.OwnerUserId = @BId


union

 -- Bounties on questions
  select
    FORMATMESSAGE('site://q/%d|%s', Question.Id, Question.Title) as Link,  
    case
      when Bounty.UserId = @AId then
        FORMATMESSAGE('%s''s %d point bounty on %s''s question', @AName, Bounty.BountyAmount, @BName)
      when Bounty.UserId = @BId then
        FORMATMESSAGE('%s''s %d point bounty on %s''s question', @BName, Bounty.BountyAmount, @AName)
    end as [What],
    Question.CreationDate as [Date A],
    Bounty.CreationDate as [Date B]
  from Posts as Question
  inner join Votes as Bounty
    on Question.id = Bounty.PostId
  where
       Question.PostTypeId = 1
   and Bounty.VoteTypeId = 8
   and (
        (Question.OwnerUserId = @AId and Bounty.UserId = @BId)
     or (Question.OwnerUserid = @BId and Bounty.UserId = @AId)
   )

union

 -- Bounties on questions the other user answered
  select
    FORMATMESSAGE('site://q/%d|%s', Answer.Id, Question.Title) as Link,  
    case
      when Bounty.UserId = @AId then
        FORMATMESSAGE('%s''s %d point bounty on a question that %s answered', @AName, Bounty.BountyAmount, @BName)
      when Bounty.UserId = @BId then
        FORMATMESSAGE('%s''s %d point bounty on a question that %s answered', @BName, Bounty.BountyAmount, @AName)
    end as [What],
    Answer.CreationDate as [Date A],
    Bounty.CreationDate as [Date B]
  from Posts as Question
  inner join Posts as Answer
    on Question.id = Answer.ParentId
  inner join Votes as Bounty
    on Question.id = Bounty.PostId
  where
       Question.PostTypeId = 1
   and Answer.PostTypeId = 2
   and Bounty.VoteTypeId = 8
   -- Bounty should be posted no earlier than 7 days before the answer,
   -- otherwise it would not have been available to award to this answer
   and DateDiff(day, Answer.CreationDate, Bounty.CreationDate) >= -7
   and (
        (Answer.OwnerUserId = @AId and Bounty.UserId = @BId)
     or (Answer.OwnerUserid = @BId and Bounty.UserId = @AId)
   )

union

 -- Bounties awarded to an answer
  select
    FORMATMESSAGE('site://q/%d|%s', Answer.Id, Question.Title) as Link,  
    case
      when Bounty.UserId = @AId then
        FORMATMESSAGE('%s''s %d point bounty awarded to %s''s answer', @AName, Bounty.BountyAmount, @BName)
      when Bounty.UserId = @BId then
        FORMATMESSAGE('%s''s %d point bounty awarded to %s''s answer', @BName, Bounty.BountyAmount, @AName)
    end as [What],
    Answer.CreationDate as [Date A],
    Awarded.CreationDate as [Date B]
  from Posts as Question
  inner join Posts as Answer
    on Question.id = Answer.ParentId
  inner join Votes as Bounty
    on Question.id = Bounty.PostId
  inner join Votes as Awarded
    on Answer.id = Awarded.PostId
  where
       Question.PostTypeId = 1
   and Answer.PostTypeId = 2
   and Bounty.VoteTypeId = 8
   and Awarded.VoteTypeId = 9
   -- Bounty start and award should have taken place within 8 days, and match amounts
   and Bounty.BountyAmount = Awarded.BountyAmount
   and DateDiff(day, Bounty.CreationDate, Awarded.CreationDate) between 0 and 8
   and (
        (Answer.OwnerUserId = @AId and Bounty.UserId = @BId)
     or (Answer.OwnerUserid = @BId and Bounty.UserId = @AId)
   )
 
union

 -- Bookmarks on questions
  select
    FORMATMESSAGE('site://q/%d|%s', Question.Id, Question.Title) as Link,  
    case
      when Bookmark.UserId = @AId then
        FORMATMESSAGE('%s bookmarked %s''s question', @AName, @BName)
      when Bookmark.UserId = @BId then
        FORMATMESSAGE('%s bookmarked %s''s question', @BName, @AName)
    end as [What],
    Question.CreationDate as [Date A],
    Bookmark.CreationDate as [Date B]
  from Posts as Question
  inner join Votes as Bookmark
    on Question.id = Bookmark.PostId
  where
       Question.PostTypeId = 1
   and Bookmark.VoteTypeId = 5
   and (
        (Question.OwnerUserId = @AId and Bookmark.UserId = @BId)
     or (Question.OwnerUserid = @BId and Bookmark.UserId = @AId)
   )
 
order by [Date A], [Date B]
edited oct 12 20
Martijn Pieters
created jan 26 11
Benjol
Enter Parameters

Options: Text-only results Include execution plan
Switch to meta site
Hold tight while we fetch your results
Results[:current/:total] Messages Graph Execution Plan
Download CSV Download XML
:records returned in :time ms:cached