← Projects
11 Jul 2026 · Erlang C, in every language

Erlang C in SQL

Part of Erlang C, in every language. Sometimes the staffing calc belongs where the call data already lives — in the database. Here’s the whole thing as PostgreSQL functions.

Same formula as the Excel and Python versions, expressed as four PL/pgSQL and SQL functions with a bundled Lanczos lgamma so it stays exact for a large centre. Create them once, and staffing becomes a query.

-- Erlang C — call-centre staffing. PostgreSQL / PL-pgSQL (bundled Lanczos lgamma).
CREATE FUNCTION lgamma(x double precision) RETURNS double precision AS $$
DECLARE
  c double precision[] := ARRAY[0.99999999999980993, 676.5203681218851,
    -1259.1392167224028, 771.32342877765313, -176.61502916214059,
    12.507343278686905, -0.13857109526572012, 9.9843695780195716e-6,
    1.5056327351493116e-7];
  a double precision; t double precision; i int;
BEGIN
  IF x < 0.5 THEN
    RETURN ln(pi() / sin(pi() * x)) - lgamma(1 - x);
  END IF;
  x := x - 1; a := c[1]; t := x + 7.5;
  FOR i IN 1..8 LOOP a := a + c[i + 1] / (x + i); END LOOP;
  RETURN 0.5 * ln(2 * pi()) + (x + 0.5) * ln(t) - t + ln(a);
END; $$ LANGUAGE plpgsql IMMUTABLE;

CREATE FUNCTION erlang_c(agents int, traffic double precision)
RETURNS double precision AS $$
DECLARE top double precision; cum double precision; rho double precision;
BEGIN
  rho := traffic / agents;
  IF rho >= 1 THEN RETURN 1; END IF;
  top := exp(agents * ln(traffic) - lgamma(agents + 1) - traffic);
  SELECT sum(exp(k * ln(traffic) - lgamma(k + 1) - traffic))
    INTO cum FROM generate_series(0, agents - 1) AS k;
  RETURN top / (top + (1 - rho) * cum);
END; $$ LANGUAGE plpgsql IMMUTABLE;

CREATE FUNCTION service_level(agents int, traffic double precision,
                              aht double precision, target double precision)
RETURNS double precision AS $$
  SELECT CASE WHEN agents <= traffic THEN 0
    ELSE 1 - erlang_c(agents, traffic) * exp(-(agents - traffic) * target / aht) END;
$$ LANGUAGE sql IMMUTABLE;

CREATE FUNCTION asa(agents int, traffic double precision, aht double precision)
RETURNS double precision AS $$
  SELECT CASE WHEN agents <= traffic THEN 'infinity'::double precision
    ELSE erlang_c(agents, traffic) * aht / (agents * (1 - traffic / agents)) END;
$$ LANGUAGE sql IMMUTABLE;

CREATE FUNCTION agents_required(traffic double precision, aht double precision,
                                sl_goal double precision, asa_goal double precision)
RETURNS int AS $$
  SELECT min(m) FROM generate_series(floor(traffic)::int + 1, 10000) AS m
   WHERE service_level(m, traffic, aht, asa_goal) >= sl_goal
     AND asa(m, traffic, aht) <= asa_goal;
$$ LANGUAGE sql IMMUTABLE;

Using it

traffic is the offered load in Erlangs — calls * aht / period. For 100 calls in 30 minutes at a 180-second handle time that’s 10, so:

SELECT agents_required(10, 180, 0.80, 20);   -- -> 14

Because the functions are IMMUTABLE, you can call them straight from a roster query — join your forecast intervals to agents_required(...) and read the required headcount for each one.

The maths, the worked example, and the same five functions in 29 other languages live on GitHub: