Solved

Need Erlang C formula Flipped & converted to SQL

Posted on 2010-11-09
7
1,314 Views
Last Modified: 2013-04-16
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.
0
Comment
Question by:okacs
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 18

Expert Comment

by:deighton
ID: 34094636
/*
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


0
 

Author Comment

by:okacs
ID: 34094760
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?
0
 
LVL 18

Accepted Solution

by:
deighton earned 500 total points
ID: 34094958
the formula you showed me was a function of A and N, doesn't that give a probability?

P = f(A, N)

but now you are saying you want N such that P = f(A, N)

you know what A and P are

---------------------------------------------------

from Wikipedia - A is traffic and N is number of servers -

Pw = ErlangC(A,N)

Pw is the probability that a customer has to wait for service

so are you perhaps saying that you want Pw to be restricted to some sort of value, .1 for example?  That would mean there is a 10% chance of waiting for a resource, then you want to know what N is to achieve that probability
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

Author Comment

by:okacs
ID: 34095238
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
0
 

Author Comment

by:okacs
ID: 34096761
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

Open in new window

0
 
LVL 18

Expert Comment

by:deighton
ID: 34100494
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.
0
 

Expert Comment

by:dstigue
ID: 39083828
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
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SELECT INTO from XML 6 45
Help with Data Warehouse / Data Marts 4 34
SSIS Column mapping 5 37
Access PS SQLSERVER from powershell 1 24
Article by: Nicole
This is a research brief on the potential colonization of humans on Mars.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question