Erlang C in SQL – Agents Required by Time Interval

I suspect there are a few people out there, who, like me, have been tasked with the responsibility of appropriately staffing a contact centre. This is a tricky task, and requires a fairly in-depth knowledge of your company’s processes and workflows to start with.

However there is some basic statistics to help you figure out the bare minimum ‘bums on seats’ required to maintain a service level at any given time.

In comes the term “Erlang”, which for those of you who aren’t familiar, is actually a unit of telephony measurement showing full utilization in a given time frame. One person in One hour who spent 60 minutes on a phone call, is 1 Erlang.

There are a few different Erlang equations, notably Erlang A, B, and C however we’re only really concerned with the C calculations since the other two are meant for something else. Don’t ask me what.

Today we want to flip it around, so we’re computing for N (the number of required agents) since we know what service level we’d like to achieve.

Imagine we have a dbo.HourlyPerformance table that looks something like this:
CREATE TABLE dbo.HourlyPerformance (
id int not null IDENTITY(1,1),
datekey int not null,
hourkey int not null,
TotalCalls int null,
AvgHandleTime float null,
ServiceLevel float null,
HoursWorked int null)
Where we needed to know when our periods of under and overstaffing have been historically, or for basic regressive analyses (i.e., forecasting). We want to be able to compare in any given hour, how many more people it would have taken to get the service level ideally using SQL as my language of choice.

(its filled with random data)

id	datekey	hourkey	TotalCalls	AvgHandleTime	ServiceLevel	HoursWorked
1	20170111	5	44	2	0.13	1
2	20170111	6	75	0	0.02	0
3	20170111	7	67	4	0.16	4
4	20170111	8	12	1	0.5	0
5	20170111	9	20	2	0.4	0
6	20170111	10	21	0	0.03	0
7	20170111	11	4	3	0.92	0
8	20170111	12	66	0	0.03	0
9	20170111	13	14	3	0.18	0
10	20170111	14	86	1	0.81	1
11	20170111	15	71	4	0.19	4
12	20170111	16	78	6	0.62	7
13	20170111	17	11	3	0.83	0
14	20170111	18	70	0	0.21	0
15	20170111	19	61	4	0.64	4
16	20170111	20	89	3	0.04	4

Which leads me to the actual functions. After quite a bit of hunting, I stumbled across this thread: https://www.experts-exchange.com/questions/26602101/Need-Erlang-C-formula-Flipped-converted-to-SQL.html where dstigue kindly posts a bunch of scalar functions which you can use in this sort of scenario.

The entire script is here: https://github.com/nzjp/SqlErlangC/


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[PowerFactorial] ( @m float,  @u float)
RETURNS float
AS
BEGIN
Declare @counter float --counter
Declare @total float -- return value   SET @counter = 1
SET @total = 0   WHILE @counter <= @u
BEGIN   SET @total = @total + Log(@m/@counter)
Set @counter= @counter + 1   END   RETURN Exp(@total)
END
GO   SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: David tigue
-- Create date: 4/12/2013
-- Description: ErlangC
-- =============================================
CREATE FUNCTION [dbo].[ErlangC]
(
-- Add the parameters for the function here
@m float  -- Number of Agents
,@u float -- Traffic intensity
)
RETURNS float
AS
BEGIN
--Source http://www.mitan.co.uk/erlang/elgcmath.htm Number 6   -- Return Variable
DECLARE @Prob Float -- Probability of Call not being answered immediately and having to wait.   -- Variables
Declare @Numerator Float -- Top of Equation
Declare @Denominator Float -- Bottom of Equation
Declare @Summation float -- Summation part of Denominator
Declare @k float -- increment for summation   --Calculate Numerator   SET @Numerator = dbo.PowerFactorial(@u,@m)   -- Start Summation with k starting at 0.
SET @k = 0
SET @Summation = 0   While @k < @m-1
Begin
SET @Summation = @Summation + dbo.PowerFactorial(@u,@k)
SET @k = @k +1
End   --Calculate denominator   SET @Denominator = dbo.PowerFactorial(@u,@m) + (1-@u/@m)*@Summation   SET @Prob = @Numerator/@Denominator   -- Return the result of the function
RETURN @Prob   END
GO   SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: David Tigue
-- Create date: 04/15/2013
-- Description: Erlang C Service
-- =============================================
CREATE FUNCTION [dbo].[ErlangCServ]
(
-- Add the parameters for the function here
@m int -- Number of Agents
,@u float -- Traffic Intensity
,@t float -- Target Time
,@ts float -- Average Call Duration
)
RETURNS float
AS
BEGIN   -- Return the result of the function
RETURN  1 - dbo.ErlangC(@m, @u) * Exp(-(@m - @u) * (@t / @ts))   END
GO   SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: David Tigue
-- Create date: 04/15/2013
-- Description: Erlang C Service
-- =============================================
CREATE FUNCTION [dbo].[ASA]
(
-- Add the parameters for the function here
@m int -- Number of Agents
,@u float -- Traffic Intensity
,@ts float -- Average Call Duration
)
RETURNS float
AS
BEGIN   -- Return the result of the function
RETURN  dbo.ErlangC(@m, @u) * @ts / (@m - @u)   END
GO   SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: David Tigue
-- Create date: 4/15/2013
-- Description: Calc Number of People Required for given Service Level
-- =============================================
CREATE FUNCTION [dbo].[ErlangCRequired]
(
-- Add the parameters for the function here
@A float -- Average Arrival Rate  (second)
,@ts float -- Average Call Duration (seconds)
,@t int -- Time Goal
,@svl float -- Service Level Goal
)
RETURNS float
AS
BEGIN
DECLARE @u float -- Traffic Intensity   Set @u = @A * @ts   DECLARE @m float --number of agents   SET @m = 2   WHILE dbo.ErlangCServ(@m,@u,@t,@ts) <= @svl
Begin
SET @m = @m + 1
END   -- Return the result of the function
RETURN @m  

END
GO

Notice the very last function [dbo].[ErlangCRequired] does something interesting, essentially it loops through the ErlangCServ until it gets to the minimum required agents that produce the requisite service level. Funnily enough, it is fairly performant and doesn’t take a hit when processing hundreds of thousands of rows.

So we can now update our table using this, or just use the scalar function to get required agents in an hour.
SELECT datekey,
       hourkey,
       totalCalls,
       AvgHandleTime,
       CASE
           WHEN totalcalls >0
                AND avgHandleTime > 0 THEN dbo.ErlangCRequired(TotalCalls / 3600.00, AvgHandleTime * 60, 20,.80)
           ELSE NULL
       END 'agentsReqd'
FROM HourlyPerformance

You can then visually establish where your peaks and troughs are, if you set it up in some ETL or something, regularly.