February 20, 2018 at 11:58 am
I have been searching forums to get the second part of my query right but to no avail, any help would be greatly appreciated here..
First part is to search for the Agent that has the least number of support calls, this is achieve by the below query:
SELECT TOP 1 assigned_to_name as Agent, COUNT(*) as Active_Calls
FROM cscases_view
GROUP BY assigned_to_name
ORDER BY 2
The result is as i need:
Agent Active Calls
Lou Jones 1
Now for the second part of my query, i need to assign the next support call to the Agent with the least number of calls. Is this possible in a simple select into statement? Sort of load balancing effect
Any SQL Expertise with this will be much appreciated!
February 20, 2018 at 2:09 pm
Post create DDL statements for the objects involved with sample data in the form of insert statements and someone will likely give it a shot.
February 20, 2018 at 2:16 pm
You can capture the ID of the person with the fewest calls, and then use it in another query:DECLARE @AgentID INT;
SELECT TOP 1
@NextAgentID = x.AgentID
FROM
( -- find the first agent with fewest calls
SELECT AgentID, COUNT(*) AS OpenCalls
FROM ActiveCalls
WHERE CallStatus = 'A'
GROUP BY AgentID
) x
ORDER BY OpenCalls ASC;
Print @NextAgentID; -- You would pass this value to the INSERT/UPDATE statement that assigned the call to this agent.
If you capture the AgentID in a variable, you could pass that to another stored procedure (or just use it later on in the same one where you do the insert
INSERT INTO ActiveCalls (<fieldlist>,AgentID) VALUES (<list>,@NextAgentID);
February 20, 2018 at 2:22 pm
Joe,
Here's my really small test setup:USE TempDB;
GO
CREATE TABLE Agent(
AgentID INT IDENTITY,
FirstName VARCHAR(15) NOT NULL,
LastName VARCHAR(20) NOT NULL
CONSTRAINT Agent_PK PRIMARY KEY (AgentID)
);
GO
CREATE TABLE ActiveCalls(
AgentID INT,
CallID INT IDENTITY,
CallStatus CHAR
CONSTRAINT ActiveCalls_PK PRIMARY KEY (CallID),
CONSTRAINT AgentID_FK FOREIGN KEY (AgentID) REFERENCES Agent(AgentID)
);
GO
INSERT INTO Agent(FirstName, LastName) VALUES ('Buddy','Holly'),('Big','Bopper'),('Richie','Valens');
INSERT INTO ActiveCalls(AgentID,CallStatus) VALUES (1,'A'),(1,'A'),(1,'C'),(2,'A'),(2,'A'),(3,'A'),(3,'A'),(3,'A');
DECLARE @AgentID INT;
SELECT TOP 1
@AgentID = x.AgentID
FROM
( -- find the first agent with fewest calls
SELECT AgentID, COUNT(*) AS OpenCalls
FROM ActiveCalls
WHERE CallStatus = 'A'
GROUP BY AgentID
) x
ORDER BY OpenCalls ASC;
Print @AgentID;
INSERT INTO ActiveCalls (AgentID, CallStatus)
VALUES (@AgentID,'A');
February 20, 2018 at 11:03 pm
Thank you for this. One last question - wouldnt it be easier to do this via a SP or Trigger?
February 21, 2018 at 12:12 am
Yes, you're right. I would absolutely do this as a stored procedure. If you post the CREATE TABLE and INSERT scripts for your Agents and ActiveCalls tables (maybe 2 records Agents, and 3 for ActiveCalls), someone can show you how to do it. (Actually, give it a try, and post your try...) I have no problem helping you learn, but you gotta get your hands dirty and try it.
You'd basically pass in all the values you intend to write to the table (except the value for @AgentID, because you don't know that yet).
You'd use some code like I provided to get the AgentID with the fewest open calls, and assign it to a variable in your code. The other values you'd pass into the stored procedure.
As I said, give it a bash, and post what you tried. If you can't get it to work (and can't figure out the errors), post what you have and explain where it didn't work. Since you are reasonably new here, read Forum Etiquette: How to post data/code on a forum to get the best help - it explains a lot - and once you learn how to ask a good question, you'll get better and tested answers.
Hope this helps!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply