How can I calculate Erlang C values in SQL Server when the float datatype is too small?

Question

We're implementing an Erlang C calculation in our data center and I hit a roadblock:
overflow errors with SQL Server's float data type.

How can I represent values in excess of 10^308 in SQL Server, in a manner where I can still perform arithmetic?

Wikipedia Article: Erlang C
Better explanation of Erlang C calculation

The Erlang C calculation answers the question, "Given a forecasted call volume and estimated handle time, how many agents should we schedule to ensure a sufficient fraction of our callers are answered within the specified time?"

For an example: Our service level is 75% of calls must be answered within 60 seconds. The chart below explores staffing for a variety of volumes and handle times that are relevant to our operation.

You can see that when the required number of agents passes about 140, SQL Server can no longer handle the size of number required.

The issue here is the exponentiation and factorial terms in the middle of the formula.
]2
For example, the calculation that leads to the first error has V=425 and AHT=600:
(425 calls/30m @ 10m/call -> 141 call hours/hour -> 141 erlangs) A=141
starting evaluation at n=142

142. For N=142, A^N is 3.02002e+305 and N! is 2.69536e+245. Finishing the calculation yields about a 6% service level, which is insufficient.
143. For N=143, A^N is 4.27836e+307 and N! is 3.85437e+247. Finishing the calculation yields about a 24% service level, which is still insufficient.
144. For N=144, A^N is 6.06101e+309 and SQL Server generates an error when I attempt to calculate it, since the float type can only handle numbers up to about 1e308.

edit:

@chtz and @dmuir gave the hint I needed.
Rather than accumulate A^i and i! separately, I accumulate them together as suggested, and th enew version works flawlessly.

SELECT 
    @acc_ai_if = @acc_ai_if * @intensity / cast(@agentcount as float)
--  @acc_if = @acc_if * @agentcount
--, @acc_ai = @acc_ai * @intensity  -- this overflows for N>143
;

Answer 1

I'm sorry to say I know nothing about SQL server, but here is an algorithm that avoids large numbers, and a C program to demonstrate it.

The key, as noted by chtz in their comment, is to avoid computing large powers and large factorials. Inroducing some arbitrary names, let

a(N) = pow( A, N)/factorial(N)
b(N) = Sum{ 0<=i<N | a(i)}

Then we can write the probability, for N>A, as

P = N/(N-A) * a(N) / (b(N) + N/(N-A) * a(N))

Note that we have

b(N+1) = b(N) + a(N)
a(N+1) = (A/(N+1))*a(N)

so we could proceed to write code based on those recursions. But both a and b get large as N increases, so I think it is better to introduce yet another function as

beta( N) = a(N)/b(N)

Then beta(1) = A, and beta(N) decreases (to zero) as N increases beyond A. In terms of beta we have

P = N/(N-A) * beta(N) / (1 + N/(N-A) * beta(N))

A little algebra gives this recursion for beta:

beta(N) = (A/N) * beta(N-1)/(1+beta(N-1))

and as noted above

beta(1) = A

Here is a C program based on these ideas:

#include    <stdio.h>
#include    <stdlib.h>

    // compute beta(toN) from A and previous value of beta
    static  double  beta_step( int A, int toN, double beta)
    {
    double  f = A/(double)toN;
        return f*beta/(1.0+beta);
    }
    int main( void)
    {
    int A = 140;
    int np = 20;    // number of probabilities to compute
        // compute values for beta at N=1..A    
    double  beta = A;
        for( int N=2; N<=A; ++N)
        {   beta = beta_step( A, N, beta);
        }
        // compute probabilities at N=A+1..A+np
        for( int i=1; i<=np; ++i)
        {
        int N = A+i;
            beta = beta_step( A, N, beta);
        double  f = (double)N/(double)i;    // == N/(N-A)
        double  prob = f*beta/(f*beta + 1.0);   
            printf( "%d\t%f\n", N, prob);
        }   
        return EXIT_SUCCESS; 
    }

I compiled this (using a rather antique gcc (4.8.5)) with

gcc -o erl -std=gnu99 -Wall erl.c