it would be much appreciated if you help me to find the problem with my sql script or provide me with alternative. I have contact center database and i'm trying to write sql script that will return the ID of the agents who were available when the call was received. the script i have is working most of the time but recently i found it doesn't work in some cases when i checked the table. one of the call was received and by checking the table which has records for calls answered by agents, i see that there was 2 agent available but the script doesn't return the ID of these 2 agents.
the call was received at 11:26:13 and the caller waited for approximately 2 minutes and half then he dropped the call at 11:28:42 at that time i see that agents with ID 1019 and 1033 were available but my sql script for some reason doesn't work for this case.
SessionID SessionSeqNum ResourceID RingTime TalkTime HoldTime CSQID StartDateTime EndDateTime
100028 0 1028 3 334 0 5 2022-03-05 11:04:35 2022-03-05 11:10:12
100029 0 1019 13 500 0 5 2022-03-05 11:05:12 2022-03-05 11:13:45>> last call for agent 1019 before customer call
100030 0 1033 4 844 0 5 2022-03-05 11:11:18 2022-03-05 11:25:26>> last call for agent 1033 before customer call
100031 0 1026 13 717 0 5 2022-03-05 11:15:15 2022-03-05 11:27:25
100032 0 1028 3 404 0 5 2022-03-05 11:19:16 2022-03-05 11:26:03
100033 0 1022 12 883 0 5 2022-03-05 11:21:15 2022-03-05 11:36:10
100034 0 1022 7 832 0 5 2022-03-05 11:21:15 2022-03-05 11:35:14
100035 0 1011 9 699 0 5 2022-03-05 11:24:53 2022-03-05 11:36:41
2022-03-05 11:26:13 2022-03-05 11:28:42>> customer call should be here
100036 0 1028 2 893 0 5 2022-03-05 11:29:21 2022-03-05 11:44:16
100037 0 1026 12 820 0 5 2022-03-05 11:29:45 2022-03-05 11:43:37
100038 0 1019 12 835 0 5 2022-03-05 11:31:08 2022-03-05 11:45:15
100039 0 1033 3 213 0 5 2022-03-05 11:35:20 2022-03-05 11:38:56
100040 0 1022 8 670 0 5 2022-03-05 11:40:31 2022-03-05 11:51:49
100041 0 1011 15 149 0 5 2022-03-05 11:41:33 2022-03-05 11:44:17
100042 0 1022 15 109 0 5 2022-03-05 11:57:29 2022-03-05 11:59:33
100043 0 1019 11 639 0 5 2022-03-05 11:58:41 2022-03-05 12:09:31
100044 0 1033 11 513 0 5 2022-03-05 11:59:44 2022-03-05 12:08:28
Below is a snippet from the script i'm using. this part should return the ID of these two available agents:
DECLARE @Counter int = 1;
While(@Counter < (select count(*) from @ResourcePool1) 1)
BEGIN
set @ID = (select AgentID
from @ResourcePool1
where RecordID = @Counter);
print 'AgentID = ' CAST(@ID as nvarchar);
IF(
( select COUNT(SessionID)
from AgentConnectionDetail as ACD
where ResourceID = @ID
and (
( @CustomerCallStartTime > ACD.StartDateTime
and ACD.EndDateTime > @CustomerCallEndTime )
or
( ACD.StartDateTime > @CustomerCallStartTime
and @CustomerCallEndTime > ACD.EndDateTime )
or
( (@CustomerCallStartTime > ACD.StartDateTime)
and (@CustomerCallStartTime < ACD.EndDateTime)
and (@CustomerCallEndTime > ACD.EndDateTime)
)
or
( (ACD.StartDateTime > @CustomerCallStartTime)
and (@CustomerCallEndTime > ACD.StartDateTime)
and (ACD.EndDateTime > @CustomerCallEndTime )
)
)
) >= 1
)
BEGIN
print 'True';
set @var2 = ( select COUNT(SessionID)
from AgentConnectionDetail as ACD
where ResourceID = @ID
and (
( @CustomerCallStartTime > ACD.StartDateTime
and ACD.EndDateTime > @CustomerCallEndTime
)
or
( ACD.StartDateTime > @CustomerCallStartTime
and @CustomerCallEndTime > ACD.EndDateTime
)
or
( ( @CustomerCallStartTime > ACD.StartDateTime)
and ( @CustomerCallStartTime < ACD.EndDateTime)
and (@CustomerCallEndTime > ACD.EndDateTime)
)
or
( ACD.StartDateTime > @CustomerCallStartTime
and ACD.EndDateTime > @CustomerCallEndTime
)
)
);
print 'Count = ' CAST(@var2 as nvarchar)
set @Counter = @Counter 1;
print 'Counter = ' CAST(@Counter as nvarchar);
END
ELSE GOTO FINISH;
END
IF(@Counter > (select count(*) from @ResourcePool1))
BEGIN
set @ID = 0;
print 'reached to go to FINISH';
GOTO FINISH;
END
just to clarify, AgentConnectionDetail (ACD) is the name of the table above and @ResourcePool1 is table variable to group those agents who receives calls from same call queue(queue ID is 5 in this case as seen in the table)
CodePudding user response：
More question than comment due to length. Your approach might be more complex than it needs to be. You can edit your existing post with more details and clarification.
If your queue of available agents is in some other table, and this AgentConnectionDetail table holds a list of ALL calls that are COMPLETED, you might consider another approach.
If you have a call coming in at ex: 2022-03-05 11:26:13 2022-03-05 11:28:42 per your example. It appears your call center entries are relatively short, such as 3-15 minutes. The query below does a check for all calls within the last hour associated with all in your resource pool. It gets a count of all calls covered, AND the most recent END OF CALL per resource pool agent. The final order is based on that most recent end of call so whoever finished their call first floats to the top of the list.
Now, if you have some other table that identifies who MAY be on another call assignment, you just need to exclude them. Otherwise, you can apply a top 1 to get whoever is the next agent who should be assigned the next call.
select
-- for each resouce ID, get the latest time the call completed
ACD.ResourceID,
max( ACD.EndDateTime ) LastCallCompleted,
count(*) CallsCovered
from
AgentConnectionDetail ACD
-- only query for those still in the random pool
JOIN @ResourcePool1 rp
on ACD.ResourceID = rp.AgentID
where
-- subtract 1 hour from the current time to see only recent without
-- having to query days worth of entries
ACD.StartDateTime > DateAdd( hour, -1, getdate() )
group by
ACD.ResourceID
order by
-- by ordering by whoever had the OLDEST FINISHED call time
-- would in essence be the next agent who should be assigned the next incoming call.
max( ACD.EndDateTime )
CodePudding user response：
The current date logic looks overly complex. I think it could be greatly simplified by searching for Agents that were NOT currently a call during the @CallStart and @CallEnd time in question.
DECLARE @CallStart DATETIME = '2022-03-05 11:26:13'
, @CallEnd DATETIME = '2022-03-05 11:28:42'
SELECT *
FROM ResourcePool1 rp
WHERE NOT EXISTS (
SELECT 1
FROM AgentConnectionDetail cd
WHERE @CallStart <= cd.EndDateTime
AND @CallEnd >= cd.StartDateTime
AND cd.ResourceId = rp.AgentId
)
Results: See note below about Agent 1028
RecordID | CSQID | AgentID
-------: | ----: | ------:
2 | 5 | 1019
5 | 5 | 1028
6 | 5 | 1033
i see that agents with ID 1019 and 1033 were available
I'm not sure if you're applying any other filters, but technically Agent 1028 seems to be available as well, as their previous call end time is 11:26:03.000 and the next was not until 2022-03-05 11:29:21.000
SessionId | ResourceId | StartDateTime | EndDateTime --------: | ---------: | :---------------------- | :---------------------- 100028 | 1028 | 2022-03-05 11:04:35.000 | 2022-03-05 11:10:12.000 100032 | 1028 | 2022-03-05 11:19:16.000 | 2022-03-05 11:26:03.000 100036 | 1028 | 2022-03-05 11:29:21.000 | 2022-03-05 11:44:16.000