Need Erlang C formula Flipped &amp; converted to SQL

Followup to Question 26596057

I'm writing a SQL query and need to calculate the # agents using the Erlang C formula but flipping it to solve for N.

Thanks.
deighton

/*
N is an integer, so there may not be a whole value of N that gives the exact value.
The following attempts to find the best value of N available in terms of absolute error.
i.e. the value of N that gets closest

*/

/****** Object:  StoredProcedure [dbo].[Erlang2]    Script Date: 11/09/2010 16:32:46 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:        ANDY DEIGHTON
-- Create date: 9/11/2010
-- Description:    ERLANG FORMULA
-- =============================================
CREATE PROCEDURE [dbo].[Erlang2]

@A  float,
@ERRLANG  float

AS
BEGIN

SET NOCOUNT ON;

DECLARE @TESTVAL float;
CREATE TABLE #BestGuess (N int, ERRLANG float, absError float);

DECLARE @N int;
SET @N = 1;

DECLARE @ERROROUT int;
SET @ERROROUT = 0;

WHILE @ERROROUT = 0
BEGIN

BEGIN TRY

DECLARE @NFACT float;
DECLARE @I int;
SET @NFACT = 1;
SET @I = 1;
WHILE @I <= @N
BEGIN
SET @NFACT = @NFACT * @I;
SET @I = @I + 1;
END

DECLARE @NUMERATOR float;
SET @NUMERATOR = (POWER(@A, @N) / @NFACT) * @N;
--work out the summation
DECLARE @SIGMA float;
SET @SIGMA = 0;
DECLARE @IFACTORIAL float;
SET @I=0;
--0! = 1 as a start
SET @IFACTORIAL = 1;
WHILE @I < @N
BEGIN
SET @SIGMA = @SIGMA + POWER(@A, @I) / @IFACTORIAL;

--increment i and calculate the next i!
SET @I = @I + 1;
SET @IFACTORIAL = @IFACTORIAL * @I;

END

DECLARE @DENOM float;
SET @DENOM = (@N - @A) * @SIGMA + POWER(@A, @N) * @N / @NFACT;

SET @TESTVAL = @NUMERATOR / @DENOM;

INSERT #BestGuess VALUES(@N , @TESTVAL , ABS(@ERRLANG - @TESTVAL));
SET @N = @N + 1;

END TRY
BEGIN CATCH
--select ERROR_MESSAGE(), @NFACT;
SET @ERROROUT = 1;
END CATCH
END

DECLARE @RETVAL int;

SELECT TOP 1 * FROM #BestGuess ORDER BY abserror ASC;

SELECT TOP 1 @RETVAL = N FROM #BestGuess ORDER BY abserror ASC;

RETURN @RETVAL;

END

GO

okacs

Geez you're fast.

Why are there 2 parameters @A and @Errlang?  In the Erlang formula, the variable A is for the Erlangs.  THe other varaible should be for Pw (the % of service level expected) ?

Am I missing something?
deighton

membership
Signing up is free and takes 30 seconds. No credit card required.

The original formula was (see screen-shot below) with inputs A (erlangs) and N (number of agents) and it returned probability Pw.  I want to flip the formula and input the probability Pw and the Erlangs, and return an INT N (the number of agents needed to maintain this probability at this erlang level of traffic).

ie:  N = (some formula of A and Pw)

ScreenShot210.jpg

Andy,

I found another way.  I just set my values out of spec and call your ErlangC SP with incremented values until I come withing spec.  See below.

``````GO
/****** Object:  StoredProcedure [dbo].[Erlang_C2]    Script Date: 11/09/2010 09:17:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:			OKACS
-- Create date:		11/9/2010
-- Description:		Finds the # agents necessary to reach a given service level
--
--					Loop Through Andy's Erlang C proc until the value meets the goal
--					and then display the resulting N
-- =============================================
ALTER PROCEDURE [dbo].[Erlang_C2]
@A  float,		-- Amount of traffic expressed in Erlangs (call arrival rate/sec  X average call time)
@P  float		-- Service Level Goal (Change of call going to hold)

AS
BEGIN

DECLARE @S float	-- Resultant Service Level
DECLARE @I int		-- Number agents required
SET @S = 99
SET @I = 1

IF (@P < 1 or @P > 99)
BEGIN
-- Error, ServiceLevel Goal cant be less than 1% or greater than 99%
SET @I = 0
END
ELSE

--	SELECT 'Looking for goal : ' + cast(@P as varchar)
WHILE ((@S >= 100-@P) and (@I < 35))	-- To a max of 35 so the function does not run forever
BEGIN
EXEC @S = [Erlang_C] @A, @I
SET @I = @I + 1;
--			SELECT 'TRYING ' + cast(@I as varchar) + ' agents for SL of '
--			SELECT @S as SL
END

SELECT @I as AgentsNeeded

END

--					Erlangs	ServiceLevel
-- EXEC [Erlang_C2]    23.31,      99
``````
BTW - I noticed if N=A, the formula returns an error due to division by zero, but this is not necessary!  It is possible to multiply the numerator and denominator by (N-A) to give the formula in the computer friendlier form

[((A^N) / N!) N] / [(N-A) sigma A^i / i! + N (A^N) / N!]

which for N = A gives the value 1

the modified form is mathematically the same as the original. but avoids asking a computer to tell us what infinity divided by infinity is.
This solution gave me overflows when trying to do the factorial part of the equation. This solution does not. Posting here for Anyone else looking for Erlang C in SQL..
ErlangC.sql