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