Skip to content
Advertisement

TSQL find number of correspondences

I have the following table:

SenderName  ReceiverName    NumberOfTime
Ajay          Vijay             1
Anthony       Mark              3
Anthony       John              5
David         Mark              2
David         John              5
David         John              3
David         John              1
John          David             2
John          David             2
John          Anthony           5
Mark          Anthony           1

Test Data:

CREATE TABLE [dbo].[tblMyTempTable](
[SenderName] [varchar](20) NULL,
[ReceiverName] [varchar](10) NULL,
[NumberOfTime] [int] NULL
) ON [PRIMARY]
GO

INSERT [dbo].[tblMyTempTable] 
  ([SenderName], [ReceiverName], [NumberOfTime]) 
VALUES 
  (N'David', N'John', 1)
  ,(N'Mark', N'Anthony', 1)
  ,(N'Ajay', N'Vijay', 1)
  ,(N'John', N'David', 2)
  ,(N'Anthony', N'Mark', 3)
  ,(N'David', N'John', 5)
  ,(N'David', N'John', 3)
  ,(N'John', N'David', 2)
  ,(N'David', N'Mark', 2)
  ,(N'John', N'Anthony', 5)
  ,(N'Anthony', N'John', 5)

I need to find out how many times correspondence has been between each pair. For example, Anthony has contacted John 5 times, John has contacted Anthony 5 times. So total correspondence between John and Anthony has been 10 times.

Similarly, David has contacted John total of 9 times, John has contacted David 4 times. So total will be 13. I have tried many things including swapping columns using substring, repalce etc. I was wondering if anyone had interesting way to achieve the wanted results. Thanks in advance.

Advertisement

Answer

You can use a query like the following:

;WITH CTE AS (
   SELECT IIF(SenderName < ReceiverName, SenderName, ReceiverName) AS first_name,
          IIF(SenderName >= ReceiverName, SenderName, ReceiverName) AS second_name,
          NumberOfTime
   FROM tblMyTempTable 
)
SELECT first_name, second_name, SUM(NumberOfTime) AS total_times
FROM CTE
GROUP BY first_name, second_name

The query uses a CTE that contains a version of the table where SenderName and ReceiverName are selected in an alphabetically sorted order.

Demo here

User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement