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.
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 50The threshold for Agent B is 10The threshold for Agent C is 60Based 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 MVPhttp://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 INTDECLARE @AgentB INTDECLARE @AgentC INTDECLARE @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'ENDThe 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, IncidentFROM cteSourceWHERE 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.IncidentFROM cteSource AS sINNER 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.AgentWHERE 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 RequirementWe 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 260 rows should be equally distributed among 2 people.Remaining 40 rows should be distributed to 3 people equallyHere the trick is Agent A: 14Agent B: 13Agent C: 13Next 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 INTDECLARE @AgentB INTDECLARE @AgentC INTDECLARE @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'ENDThe 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 made1. Added a new field IsLastAssigned (bit) in Agent table to indicate agent who got assigned last in previous update2. 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 update3. AgentType is filed in Agent to indicate type of operation (EMail,Call etc)DECLARE @targetcount int,@Agentcount int,@LastAgent intSELECT @lastAgent = AgentIDFROM AgentWHERE IsLastAssigned = 1SELECT @targetcount=SUM(target),@agentcount=count(distinct AgentID)FROM AgentWHERE AgentType='Email'AND target IS NOT NULL;With CTEAS(SELECT a.*,TargetTotalFROM Agent aCROSS APPLY (SELECT SUM(Target) AS TargetTotal FROM Agent WHERE AgentID <= a.AgentID AND AgentID <> @LastAgent )bWHERE Target IS NOT NULLAND AgentID <> @LastAgentUNION ALLSELECT *,SUM(Target) OVER ()FROM AgentWHERE AgentID = @LastAgentAND Target IS NOT NULL)UPDATE tSET Assignee = c.AgentNameFROM (SELECT ROW_NUMBER() OVER (ORDER BY IncidentNumber) AS Seq,Assignee FROM tableBWHERE Assignee IS NULL)tINNER 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 )ctON t.Seq BETWEEN c.PrevTotal AND c.TargetTotalWHERE t.Seq < = @targetcount--set remaining if any with agents without targetsSELECT @agentcount=COUNT(DISTINCT AGentID)FROM AgentWHERE AgentType='Email'AND Target IS NULLUPDATE tSET Assignee = c.AgentNameFROM (SELECT NTILE(@agentcount) OVER (ORDER BY IncidentNumber) AS Seq,Assignee FROM tableBWHERE Assignee IS NULL)tINNER JOIN (SELECT ROW_NUMBER() OVER (ORDER BY AgentID) AS Seq,* FROM Agent WHERE AgentType='Email' AND Target IS NULL )aON a.Seq = t.SeqUPDATE AgentSET IsLastAssigned = 0WHERE IsLastAssigned=1UPDATE AgentSET IsLastAssigned=1WHERE AgentName = (SELECT TOP 1 Assignee FROM tableB WHERE Assignee IS NOT NULL ORDER BY IncidentNumber DESC ) ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://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_AssignmentSELECT [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]GOTable 2: --> Email_Assignment_AssigneeSELECT [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]GOEvery 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 made1. Added a new field IsLastAssigned (bit) in Agent table to indicate agent who got assigned last in previous update2. 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 update3. AgentType is filed in Agent to indicate type of operation (EMail,Call etc)DECLARE @targetcount int,@Agentcount int,@LastAgent intSELECT @lastAgent = AgentIDFROM AgentWHERE IsLastAssigned = 1SELECT @targetcount=SUM(target),@agentcount=count(distinct AgentID)FROM AgentWHERE AgentType='Email'AND target IS NOT NULL;With CTEAS(SELECT a.*,TargetTotalFROM Agent aCROSS APPLY (SELECT SUM(Target) AS TargetTotal FROM Agent WHERE AgentID <= a.AgentID AND AgentID <> @LastAgent )bWHERE Target IS NOT NULLAND AgentID <> @LastAgentUNION ALLSELECT *,SUM(Target) OVER ()FROM AgentWHERE AgentID = @LastAgentAND Target IS NOT NULL)UPDATE tSET Assignee = c.AgentNameFROM (SELECT ROW_NUMBER() OVER (ORDER BY IncidentNumber) AS Seq,Assignee FROM tableBWHERE Assignee IS NULL)tINNER 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 )ctON t.Seq BETWEEN c.PrevTotal AND c.TargetTotalWHERE t.Seq < = @targetcount--set remaining if any with agents without targetsSELECT @agentcount=COUNT(DISTINCT AGentID)FROM AgentWHERE AgentType='Email'AND Target IS NULLUPDATE tSET Assignee = c.AgentNameFROM (SELECT NTILE(@agentcount) OVER (ORDER BY IncidentNumber) AS Seq,Assignee FROM tableBWHERE Assignee IS NULL)tINNER JOIN (SELECT ROW_NUMBER() OVER (ORDER BY AgentID) AS Seq,* FROM Agent WHERE AgentType='Email' AND Target IS NULL )aON a.Seq = t.SeqUPDATE AgentSET IsLastAssigned = 0WHERE IsLastAssigned=1UPDATE AgentSET IsLastAssigned=1WHERE AgentName = (SELECT TOP 1 Assignee FROM tableB WHERE Assignee IS NOT NULL ORDER BY IncidentNumber DESC ) ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs
|
|
|
|
|
|
|
|