How to use cross apply to return 2 call records for each agent with least talking time?-CodePudding

I have table stores record for each call including the ID of the call(SessionID), Talking Time and the ID of the agent who answered/missed the call(ResourceID). For each agent I want to return the shortest 2 call records. I have 39 agents receiving calls so i'm expecting to have 78 records in the output. I used the query below with cross apply but it is duplicating the 2 records for each agent 62 times(i'm using where TalkTime > 0 to return answered calls only):

select ACD1.ResourceID,CONCAT(R.FirstName,' ',R.LastName) as [Agent Name], A.SessionID, A.TalkTime
from AgentConnectionDetail as ACD1
CROSS APPLY(select top(2) ACD2.ResourceID, ACD2.SessionID, ACD2.TalkTime 
            from AgentConnectionDetail as ACD2
            where TalkTime > 0
            and ACD1.ResourceID = ACD2.ResourceID
            order by ACD2.TalkTime,ACD2.SessionID ) as A
JOIN Resource as R
on ACD1.ResourceID = R.ResourceID

Actual output:

ResourceID  Agent Name  SessionID   TalkTime
1001    Liza Mccdonald  177235  31
1001    Liza Mccdonald  218346  70
1001    Liza Mccdonald  177235  31
1001    Liza Mccdonald  218346  70
.
.
1001    Liza Mccdonald  177235  31
1001    Liza Mccdonald  218346  70 >> record number = 124
1002    Ashly Peter 193873  57
1002    Ashly Peter 267030  44
1002    Ashly Peter 193873  57
1002    Ashly Peter 267030  44
.
.
1002    Ashly Peter 193873  57
1002    Ashly Peter 267030  44 >> record number = 257
.
.
1039    Scarlett Johnson    171696  24
1039    Scarlett Johnson    165479  70
1039    Scarlett Johnson    171696  24
1039    Scarlett Johnson    165479  70>> Last record in the output = 535362

Expected Output:

ResourceID  Agent Name  SessionID   TalkTime
1001    Liza Mccdonald  177235  31
1001    Liza Mccdonald  218346  70
1002    Ashly Peter 193873  57
1002    Ashly Peter 267030  44
.
.
.
1039    Scarlett Johnson    171696  24
1039    Scarlett Johnson    165479  70

Any idea what is wrong??

CodePudding user response：

You could apply directly on the Resource table.

Assuming that the ResourceID is the primary key in that table, thus unique.

SELECT 
  R.ResourceID
, CONCAT(R.FirstName,' ',R.LastName) AS [Agent Name]
, A.SessionID
, A.TalkTime
FROM Resource AS R
CROSS APPLY (
  SELECT TOP(2) ACD.ResourceID, ACD.SessionID, ACD.TalkTime 
  FROM AgentConnectionDetail AS ACD
  WHERE ACD.TalkTime > 0
    AND ACD.ResourceID = R.ResourceID
  ORDER BY ACD.TalkTime, ACD.SessionID
) A;

Another method is to use the ROW_NUMBER window function.

;WITH CTE_ACD AS (
  SELECT ResourceID, SessionID, TalkTime
  , RN = ROW_NUMBER() OVER (PARTITION BY ResourceID ORDER BY TalkTime, SessionID) 
  FROM AgentConnectionDetail
  WHERE TalkTime > 0
) 
SELECT 
  R.ResourceID
, CONCAT(R.FirstName,' ',R.LastName) AS [Agent Name]
, A.SessionID
, A.TalkTime
FROM CTE_ACD A
JOIN Resource R ON R.ResourceID = A.ResourceID
WHERE A.RN <= 2;