Evaluate performance and prevalence for any series of diagnostics tests, incl. SARS

with tests as 
(
select 
	:report_id::text as report_id, -- Feel free to call with any string, e.g. date-string
	:tests as tests_performed, -- integer
	:cases as positives_reported --integer
),

permutations as
(
select
	(prevalence::numeric / 1000)::numeric as prevalence,
	(sensitivity::numeric / 1000)::numeric as sensitivity,
	(specificity::numeric / 1000)::numeric as specificity
from
	generate_series(1, 500, 1) as prevalence,
	generate_series(30, 999, 5) as sensitivity,
	generate_series(75, 999, 5) as specificity
),

matrices as
(
select 
	t.report_id,
	t.tests_performed,
	t.positives_reported,
	round(prevalence, 2) as prevalence, --just for cosmetic purposes
	round(sensitivity, 3) as sensitivity,
	round(specificity, 3) as specificity,
	(t.tests_performed * prevalence)::int as has_disease, --calculation with full precision for data type numeric, but
	(t.tests_performed * (1 - prevalence))::int as hasnot_disease, --casting to integer for cosmetic purposes
	(t.tests_performed * prevalence * sensitivity)::int as true_positives,
	(t.tests_performed * (1 - prevalence) * specificity)::int as true_negatives
from 
	tests t,
	permutations p
)

select 
	*,
	hasnot_disease - true_negatives as false_positives,
	has_disease - true_positives as false_negatives
from 
	matrices
where
	(true_positives + (hasnot_disease - true_negatives)) = positives_reported
order by
	report_id,
	prevalence,
	sensitivity,
	specificity