PostgreSQL query template to compute statistics for a single employee in NCLS Development's Orca solution

employee_stats.sql

SELECT
	emce_date,
	ttce_cert_fk,
	CASE
		WHEN emce_date IS null OR emce_date > max(trng_date + trty_validity * INTERVAL '1 month')::date
			THEN max(trng_date + trty_validity * INTERVAL '1 month')::date
		ELSE emce_date
	END AS expiry,
	CASE
		WHEN
			CASE
				WHEN emce_date IS null OR emce_date > max(trng_date + trty_validity * INTERVAL '1 month')::date
					THEN max(trng_date + trty_validity * INTERVAL '1 month')::date
				ELSE emce_date
			END >= current_date + INTERVAL '6 month'
			THEN 'success'
		WHEN
			CASE
				WHEN emce_date IS null OR emce_date > max(trng_date + trty_validity * INTERVAL '1 month')::date
					THEN max(trng_date + trty_validity * INTERVAL '1 month')::date
				ELSE emce_date
			END >= current_date
			THEN 'warning'
		ELSE 'danger'
	END AS validity

FROM
	trainingtypes_certificates
	JOIN trainingtypes ON trty_pk = ttce_trty_fk
	JOIN trainings ON trng_trty_fk = trty_pk
	JOIN trainings_employees ON trem_trng_fk = trng_pk
	LEFT JOIN employees_certificates_optout ON emce_empl_fk = trem_empl_fk AND emce_cert_fk = ttce_cert_fk

WHERE
	trem_empl_fk = '00009408'
	AND trem_outcome = 'VALIDATED'
	AND trng_date <= current_date

GROUP BY (emce_date, ttce_cert_fk)