Query to Assign Case based on Threshold

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Query to Assign Case based on Threshold

Author

Topic

cvkk2010
Starting Member

5 Posts

Posted - 2013-06-24 : 02:04:48

Team,

Need some help,

We have four/five people who works on email incidents. I have to write a query in such a way the incidents should be assigned to those four/five people based on priority.

For example:
The threshold for Agent A is 50
The threshold for Agent B is 10
The threshold for Agent C is 60

Based on the Threshold value it has to assign only 50 incidents for Agent A.

Please let me know what kind of query i can use and it will appreciated if you give some example query.

Thanks in Advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-24 : 02:17:17

so is it like all first 50 to A, next 10 to B etc? or is it round robin based? ie A,B,C then again A,B,C etc until it hits threshold?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2013-06-24 : 02:32:39

Perhaps something like this:
DECLARE @AgentA INT
DECLARE @AgentB INT
DECLARE @AgentC INT
DECLARE @Rand INT
SELECT
@AgentA = 50,
@AgentB = 10,
@AgentC = 60,
@Rand = CAST(RAND() * (@AgentA + @AgentB + @AgentC) AS INT)

SELECT CASE
WHEN @Rand <= @AgentA THEN 'Agent A'
WHEN @Rand BETWEEN (@AgentA + 1) AND (@AgentA + @AgentB) THEN 'Agent B'
ELSE 'Agent C'
END

The above example distributes it randomly giving the guys with a higher threshold more chance of being assigned than the guys with a lower threshold.

Duane.
http://ditchiecubeblog.wordpress.com/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-06-24 : 02:44:23

[code]WITH cteSource(Agent, Incident, rnk)
AS (
SELECT Type AS Agent,
Number AS Incident,
ROW_NUMBER() OVER (PARTITION BY Type ORDER BY (SELECT NEWID())) AS rnk
FROM master.dbo.spt_values
WHERE Type IN ('P', 'EOD', 'EOB')
)
SELECT Agent,
Incident
FROM cteSource
WHERE CASE
WHEN Agent = 'P' AND rnk <= 60 THEN 1
WHEN Agent = 'EOD' AND rnk <= 10 THEN 1
WHEN Agent = 'EOB' AND rnk <= 50 THEN 1
ELSE 0
END = 1[/code]

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-06-24 : 02:46:01

[code]WITH cteSource(Agent, Incident, rnk)
AS (
SELECT Type AS Agent,
Number AS Incident,
ROW_NUMBER() OVER (PARTITION BY Type ORDER BY (SELECT NEWID())) AS rnk
FROM master.dbo.spt_values
WHERE Type IN ('P', 'EOD', 'EOB')
)
SELECT s.Agent,
s.Incident
FROM cteSource AS s
INNER JOIN (
SELECT 'p' AS Agent, 60 AS Threshold UNION ALL
SELECT 'EOD' AS Agent, 10 AS Threshold UNION ALL
SELECT 'EOB' AS Agent, 50 AS Threshold
) AS w ON w.Agent = s.Agent
WHERE s.rnk <= w.Threshold[/code]

N 56°04'39.26"
E 12°55'05.63"

cvkk2010
Starting Member

5 Posts

Posted - 2013-06-25 : 02:25:27

Hi Ditch,

Thanks for your prompt Response.

Let me brief you about my Requirement

We have 10 Agents who takes Calls and feed the Account number and Call type in Table A.

Every Day around 7AM the Data in the table 1 is inserted into table B and the Assignee field will be null by default.

We have 5 Agents who work on Emails.

Now in these 5 people 2 has a target that max they can work upto 30 emails..

Example:

If 100 Rows are feed in table 2

60 rows should be equally distributed among 2 people.

Remaining 40 rows should be distributed to 3 people equally

Here the trick is

Agent A: 14
Agent B: 13
Agent C: 13

Next time if we run the update with new set of data the update should start from Agent B.

Please let me know if you are not clear..

Thanks in Advance..

Your help is highly appreciated. :)

quote:
Originally posted by ditch

Perhaps something like this:
DECLARE @AgentA INT
DECLARE @AgentB INT
DECLARE @AgentC INT
DECLARE @Rand INT
SELECT
@AgentA = 50,
@AgentB = 10,
@AgentC = 60,
@Rand = CAST(RAND() * (@AgentA + @AgentB + @AgentC) AS INT)

SELECT CASE
WHEN @Rand <= @AgentA THEN 'Agent A'
WHEN @Rand BETWEEN (@AgentA + 1) AND (@AgentA + @AgentB) THEN 'Agent B'
ELSE 'Agent C'
END

The above example distributes it randomly giving the guys with a higher threshold more chance of being assigned than the guys with a lower threshold.

Duane.
http://ditchiecubeblog.wordpress.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-25 : 04:37:29

the stub will look something like below (untested as i dont have a sql box nearby now so there may be some syntax issues)

Some assumptions i made

1. Added a new field IsLastAssigned (bit) in Agent table to indicate agent who got assigned last in previous update
2. Assumed IncidentNumber as PK for tableB and AgentID as PK for AgentTable. Also I've assumed Assignee to be of type varchar and containing AGentName. If you need to store ID make it int and use column AgentID in update
3. AgentType is filed in Agent to indicate type of operation (EMail,Call etc)


DECLARE @targetcount int,@Agentcount int,@LastAgent int

SELECT @lastAgent = AgentID
FROM Agent
WHERE IsLastAssigned = 1


SELECT @targetcount=SUM(target),
@agentcount=count(distinct AgentID)
FROM Agent
WHERE AgentType='Email'
AND target IS NOT NULL

;With CTE
AS
(
SELECT a.*,TargetTotal
FROM Agent a
CROSS APPLY (SELECT SUM(Target) AS TargetTotal
             FROM Agent
             WHERE AgentID <= a.AgentID
             AND AgentID <> @LastAgent
             )b
WHERE Target IS NOT NULL
AND AgentID <> @LastAgent
UNION ALL
SELECT *,SUM(Target) OVER ()
FROM Agent
WHERE AgentID = @LastAgent
AND Target IS NOT NULL
)

UPDATE t
SET Assignee = c.AgentName
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY IncidentNumber) AS Seq,Assignee 
FROM tableB
WHERE Assignee IS NULL
)t
INNER JOIN (SELECT c.*,CASE WHEN c.IsLastAssigned =0 THEN COALESCE(PrevTotal,1) ELSE TargetTotal - Target END
            FROM CTE c
            OUTER APPLY (SELECT TOP 1 TargetTotal AS PrevTotal
                         FROM CTE
                         WHERE AgentID < c.AgentID
                         ORDER BY AgentID DESC)c1
            )ct
ON t.Seq BETWEEN c.PrevTotal AND c.TargetTotal
WHERE t.Seq < = @targetcount

--set remaining if any with agents without targets

SELECT @agentcount=COUNT(DISTINCT AGentID)
FROM Agent
WHERE AgentType='Email'
AND Target IS NULL

UPDATE t
SET Assignee = c.AgentName
FROM (
SELECT NTILE(@agentcount) OVER (ORDER BY IncidentNumber) AS Seq,Assignee 
FROM tableB
WHERE Assignee IS NULL
)t
INNER JOIN (SELECT ROW_NUMBER() OVER (ORDER BY AgentID) AS Seq,*
            FROM Agent
            WHERE AgentType='Email'
            AND Target IS NULL
           )a
ON a.Seq = t.Seq


UPDATE Agent
SET IsLastAssigned = 0
WHERE IsLastAssigned=1

UPDATE Agent
SET IsLastAssigned=1
WHERE AgentName = (SELECT TOP 1 Assignee 
                   FROM tableB
                   WHERE Assignee IS NOT NULL
                   ORDER BY IncidentNumber DESC
                  )

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

cvkk2010
Starting Member

5 Posts

Posted - 2013-06-28 : 23:45:09

Hi Vikash,

Thanks for your reply,

Please find the table schema below.

Table 1: --> Email_Assignment

SELECT [Account_Number]
,[Call_Type]
,[Call_Status]
,[Agent_Name]
,[Assignee]
,[Create_Date]
,[Case_Id]
,[Close_Date]
,[Agent_Comments]
,[Assignee_Comments]
,[ID]
FROM [DFS_Tools].[dbo].[Email_Assignment]
GO

Table 2: --> Email_Assignment_Assignee

SELECT [Account_Number]
,[Call_Type]
,[Call_Status]
,[Agent_Name]
,[Create_Date]
,[Agent_Comments]
,[Case_Id]
,[Close_Date]
,[Assignee]
,[Assignee_Comments]
,[ID]
FROM [DFS_Tools].[dbo].[Email_Assignment_Assignee]
GO

Every day the data in Table 1 is moved to Table 2 and the Assignee column should get updated as per the requirement.

Since i am new to SQL i was not able to understand the code.

Please guide me

Thanks :)

quote:

Originally posted by visakh16

the stub will look something like below (untested as i dont have a sql box nearby now so there may be some syntax issues)

Some assumptions i made

1. Added a new field IsLastAssigned (bit) in Agent table to indicate agent who got assigned last in previous update
2. Assumed IncidentNumber as PK for tableB and AgentID as PK for AgentTable. Also I've assumed Assignee to be of type varchar and containing AGentName. If you need to store ID make it int and use column AgentID in update
3. AgentType is filed in Agent to indicate type of operation (EMail,Call etc)


DECLARE @targetcount int,@Agentcount int,@LastAgent int

SELECT @lastAgent = AgentID
FROM Agent
WHERE IsLastAssigned = 1


SELECT @targetcount=SUM(target),
@agentcount=count(distinct AgentID)
FROM Agent
WHERE AgentType='Email'
AND target IS NOT NULL

;With CTE
AS
(
SELECT a.*,TargetTotal
FROM Agent a
CROSS APPLY (SELECT SUM(Target) AS TargetTotal
             FROM Agent
             WHERE AgentID <= a.AgentID
             AND AgentID <> @LastAgent
             )b
WHERE Target IS NOT NULL
AND AgentID <> @LastAgent
UNION ALL
SELECT *,SUM(Target) OVER ()
FROM Agent
WHERE AgentID = @LastAgent
AND Target IS NOT NULL
)

UPDATE t
SET Assignee = c.AgentName
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY IncidentNumber) AS Seq,Assignee 
FROM tableB
WHERE Assignee IS NULL
)t
INNER JOIN (SELECT c.*,CASE WHEN c.IsLastAssigned =0 THEN COALESCE(PrevTotal,1) ELSE TargetTotal - Target END
            FROM CTE c
            OUTER APPLY (SELECT TOP 1 TargetTotal AS PrevTotal
                         FROM CTE
                         WHERE AgentID < c.AgentID
                         ORDER BY AgentID DESC)c1
            )ct
ON t.Seq BETWEEN c.PrevTotal AND c.TargetTotal
WHERE t.Seq < = @targetcount

--set remaining if any with agents without targets

SELECT @agentcount=COUNT(DISTINCT AGentID)
FROM Agent
WHERE AgentType='Email'
AND Target IS NULL

UPDATE t
SET Assignee = c.AgentName
FROM (
SELECT NTILE(@agentcount) OVER (ORDER BY IncidentNumber) AS Seq,Assignee 
FROM tableB
WHERE Assignee IS NULL
)t
INNER JOIN (SELECT ROW_NUMBER() OVER (ORDER BY AgentID) AS Seq,*
            FROM Agent
            WHERE AgentType='Email'
            AND Target IS NULL
           )a
ON a.Seq = t.Seq


UPDATE Agent
SET IsLastAssigned = 0
WHERE IsLastAssigned=1

UPDATE Agent
SET IsLastAssigned=1
WHERE AgentName = (SELECT TOP 1 Assignee 
                   FROM tableB
                   WHERE Assignee IS NOT NULL
                   ORDER BY IncidentNumber DESC
                  )

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources