Advanced Reports
Doctor performance analytics
This report identifies which doctors are performing best over a given date range (report_start–report_end), combining several independent activity streams into one weighted score:
- Appointment volume and completion rate.
- Procedure volume, revenue, and how often a performed procedure has a documented result on file.
- Lab test volume, revenue, and how often an ordered test type has a result available.
- Referral volume and whether referred patients actually followed up with the receiving doctor.
- Breadth of care, measured as the number of distinct patients touched across procedures, lab tests, and appointments combined.
Six factors feed into one weighted performance_score: appointment completion, procedure documentation, lab result availability, and referral follow-up each contribute a percentage-based rate, while procedure volume and patient breadth contribute capped counts (so one very busy doctor can't dominate the score). Doctors are then ranked with DENSE_RANK(), so tied scores share a rank instead of skipping numbers.
SQL
WITH params AS (
SELECT
CAST('2026-05-16' AS DATE) AS report_start,
CAST('2026-08-26' AS DATE) AS report_end
),
documented_procedure_ids AS (
SELECT DISTINCT procedure_id FROM procedure_results
),
documented_test_ids AS (
SELECT DISTINCT test_id FROM lab_results
),
doctor_appointments AS (
SELECT
d.doctor_id, d.first_name, d.last_name, d.email_address,
ds.specialization_name, dept.department_name,
COUNT(*) AS total_appointments,
COUNT(*) FILTER (WHERE a.status = 'COMPLETED') AS completed_appointments,
COUNT(*) FILTER (WHERE a.status = 'CANCELLED') AS cancelled_appointments,
ROUND(100.0 * COUNT(*) FILTER (WHERE a.status = 'COMPLETED') / NULLIF(COUNT(*), 0), 2) AS appointment_completion_rate
FROM doctors d
JOIN appointments a ON d.doctor_id = a.doctor_id
JOIN doctor_specialization ds ON d.specialization_id = ds.specialization_id
JOIN departments dept ON d.department_id = dept.department_id
JOIN params p ON a.appointment_date >= p.report_start AND a.appointment_date <= p.report_end
GROUP BY d.doctor_id, d.first_name, d.last_name, d.email_address, ds.specialization_name, dept.department_name
),
doctor_procedures AS (
SELECT
d.doctor_id,
COUNT(*) AS procedures_performed,
ROUND(AVG(proc.cost)::numeric, 2) AS avg_procedure_cost,
SUM(proc.cost) AS total_procedure_revenue,
ROUND(100.0 * COUNT(DISTINCT dpi.procedure_id) / NULLIF(COUNT(DISTINCT pp.procedure_id), 0), 2) AS procedure_documentation_rate
FROM doctors d
JOIN performed_procedures pp ON d.doctor_id = pp.doctor_id
JOIN procedures proc ON pp.procedure_id = proc.procedure_id
LEFT JOIN documented_procedure_ids dpi ON dpi.procedure_id = pp.procedure_id
JOIN params p ON pp.procedure_date >= p.report_start AND pp.procedure_date <= p.report_end
GROUP BY d.doctor_id
),
doctor_lab_tests AS (
SELECT
d.doctor_id,
COUNT(*) AS lab_tests_ordered,
ROUND(AVG(lt.cost)::numeric, 2) AS avg_test_cost,
SUM(lt.cost) AS total_lab_revenue,
ROUND(100.0 * COUNT(DISTINCT dti.test_id) / NULLIF(COUNT(DISTINCT plt.test_id), 0), 2) AS lab_result_availability_rate
FROM doctors d
JOIN performed_lab_tests plt ON d.doctor_id = plt.doctor_id
JOIN lab_tests lt ON plt.test_id = lt.test_id
LEFT JOIN documented_test_ids dti ON dti.test_id = plt.test_id
JOIN params p ON plt.test_date >= p.report_start AND plt.test_date <= p.report_end
GROUP BY d.doctor_id
),
doctor_referrals AS (
SELECT
d.doctor_id,
COUNT(DISTINCT ref.referral_id) AS referrals_made,
ROUND(100.0 * COUNT(DISTINCT CASE WHEN EXISTS (
SELECT 1 FROM appointments fa
WHERE fa.doctor_id = ref.to_doctor_id
AND fa.patient_id = mr.patient_id
AND fa.appointment_date > ref.referral_date
) THEN ref.referral_id END) / NULLIF(COUNT(DISTINCT ref.referral_id), 0), 2) AS referral_followup_rate
FROM doctors d
JOIN referrals ref ON d.doctor_id = ref.from_doctor_id
JOIN medical_records mr ON ref.record_id = mr.record_id
JOIN params p ON ref.referral_date >= p.report_start AND ref.referral_date <= p.report_end
GROUP BY d.doctor_id
),
doctor_patient_touchpoints AS (
SELECT pp.doctor_id, pp.patient_id FROM performed_procedures pp
JOIN params p ON pp.procedure_date >= p.report_start AND pp.procedure_date <= p.report_end
UNION
SELECT plt.doctor_id, plt.patient_id FROM performed_lab_tests plt
JOIN params p ON plt.test_date >= p.report_start AND plt.test_date <= p.report_end
UNION
SELECT a.doctor_id, a.patient_id FROM appointments a
JOIN params p ON a.appointment_date >= p.report_start AND a.appointment_date <= p.report_end
),
doctor_unique_patients AS (
SELECT doctor_id, COUNT(DISTINCT patient_id) AS total_unique_patients
FROM doctor_patient_touchpoints
GROUP BY doctor_id
),
doctor_scores AS (
SELECT
da.doctor_id, da.first_name, da.last_name, da.email_address,
da.specialization_name, da.department_name,
da.total_appointments, da.completed_appointments, da.cancelled_appointments, da.appointment_completion_rate,
COALESCE(dp.procedures_performed, 0) AS procedures_performed,
COALESCE(dp.avg_procedure_cost, 0) AS avg_procedure_cost,
COALESCE(dp.total_procedure_revenue, 0) AS total_procedure_revenue,
COALESCE(dp.procedure_documentation_rate, 0) AS procedure_documentation_rate,
COALESCE(dl.lab_tests_ordered, 0) AS lab_tests_ordered,
COALESCE(dl.avg_test_cost, 0) AS avg_test_cost,
COALESCE(dl.total_lab_revenue, 0) AS total_lab_revenue,
COALESCE(dl.lab_result_availability_rate, 0) AS lab_result_availability_rate,
COALESCE(dr.referrals_made, 0) AS referrals_made,
COALESCE(dr.referral_followup_rate, 0) AS referral_followup_rate,
COALESCE(dup.total_unique_patients, 0) AS total_unique_patients,
ROUND(
COALESCE(da.appointment_completion_rate, 0) * 0.20
+ COALESCE(dp.procedure_documentation_rate, 0) * 0.20
+ COALESCE(dl.lab_result_availability_rate, 0) * 0.15
+ COALESCE(dr.referral_followup_rate, 0) * 0.15
+ LEAST(COALESCE(dp.procedures_performed, 0), 50) * 0.30
+ LEAST(COALESCE(dup.total_unique_patients, 0), 100) * 0.20
, 2) AS performance_score
FROM doctor_appointments da
LEFT JOIN doctor_procedures dp ON da.doctor_id = dp.doctor_id
LEFT JOIN doctor_lab_tests dl ON da.doctor_id = dl.doctor_id
LEFT JOIN doctor_referrals dr ON da.doctor_id = dr.doctor_id
LEFT JOIN doctor_unique_patients dup ON da.doctor_id = dup.doctor_id
)
SELECT *,
DENSE_RANK() OVER (ORDER BY performance_score DESC) AS performance_rank
FROM doctor_scores
ORDER BY performance_rank, last_name, first_name;
Relational Algebra
Params ← {(report_start, report_end)}
DocumentedProcedureIds ← π_{procedure_id} (procedure_results)
DocumentedTestIds ← π_{test_id} (lab_results)
DoctorAppointments ←
γ
doctor_id := d.doctor_id; first_name := d.first_name; last_name := d.last_name;
email_address := d.email_address; specialization_name := ds.specialization_name;
department_name := dept.department_name;
total_appointments := COUNT(*);
completed_appointments := COUNT(*) FILTER (a.status = 'COMPLETED');
cancelled_appointments := COUNT(*) FILTER (a.status = 'CANCELLED');
appointment_completion_rate := ROUND(100.0 * COUNT(*) FILTER (a.status = 'COMPLETED') / COUNT(*), 2)
(
σ (a.appointment_date ≥ p.report_start ∧ a.appointment_date ≤ p.report_end)
(
(
(doctors d ⨝ (d.doctor_id = a.doctor_id) appointments a)
⨝ (d.specialization_id = ds.specialization_id) doctor_specialization ds
)
⨝ (d.department_id = dept.department_id) departments dept
× Params p
)
)
DoctorProcedures ←
γ
doctor_id := d.doctor_id;
procedures_performed := COUNT(*);
avg_procedure_cost := ROUND(AVG(proc.cost), 2);
total_procedure_revenue := SUM(proc.cost);
procedure_documentation_rate := ROUND(100.0 * COUNT_DISTINCT(dpi.procedure_id) / COUNT_DISTINCT(pp.procedure_id), 2)
(
σ (pp.procedure_date ≥ p.report_start ∧ pp.procedure_date ≤ p.report_end)
(
(
(doctors d ⨝ (d.doctor_id = pp.doctor_id) performed_procedures pp)
⨝ (pp.procedure_id = proc.procedure_id) procedures proc
)
⟕ (pp.procedure_id = dpi.procedure_id) DocumentedProcedureIds dpi
× Params p
)
)
DoctorLabTests ←
γ
doctor_id := d.doctor_id;
lab_tests_ordered := COUNT(*);
avg_test_cost := ROUND(AVG(lt.cost), 2);
total_lab_revenue := SUM(lt.cost);
lab_result_availability_rate := ROUND(100.0 * COUNT_DISTINCT(dti.test_id) / COUNT_DISTINCT(plt.test_id), 2)
(
σ (plt.test_date ≥ p.report_start ∧ plt.test_date ≤ p.report_end)
(
(
(doctors d ⨝ (d.doctor_id = plt.doctor_id) performed_lab_tests plt)
⨝ (plt.test_id = lt.test_id) lab_tests lt
)
⟕ (plt.test_id = dti.test_id) DocumentedTestIds dti
× Params p
)
)
FollowedUpReferrals ←
π_{referral_id}
(
σ (fa.doctor_id = ref.to_doctor_id ∧ fa.patient_id = mr.patient_id ∧ fa.appointment_date > ref.referral_date)
(
(referrals ref ⨝ (ref.record_id = mr.record_id) medical_records mr)
× appointments fa
)
)
DoctorReferrals ←
γ
doctor_id := d.doctor_id;
referrals_made := COUNT_DISTINCT(ref.referral_id);
referral_followup_rate := ROUND(100.0 * COUNT_DISTINCT(fur.referral_id) / COUNT_DISTINCT(ref.referral_id), 2)
(
σ (ref.referral_date ≥ p.report_start ∧ ref.referral_date ≤ p.report_end)
(
(
(doctors d ⨝ (d.doctor_id = ref.from_doctor_id) referrals ref)
⨝ (ref.record_id = mr.record_id) medical_records mr
)
⟕ (ref.referral_id = fur.referral_id) FollowedUpReferrals fur
× Params p
)
)
DoctorPatientTouchpoints ←
π_{doctor_id, patient_id}
(
σ (pp.procedure_date ≥ p.report_start ∧ pp.procedure_date ≤ p.report_end) (performed_procedures pp × Params p)
)
∪
π_{doctor_id, patient_id}
(
σ (plt.test_date ≥ p.report_start ∧ plt.test_date ≤ p.report_end) (performed_lab_tests plt × Params p)
)
∪
π_{doctor_id, patient_id}
(
σ (a.appointment_date ≥ p.report_start ∧ a.appointment_date ≤ p.report_end) (appointments a × Params p)
)
DoctorUniquePatients ←
γ doctor_id; total_unique_patients := COUNT_DISTINCT(patient_id)
(DoctorPatientTouchpoints)
DoctorScores ←
π
doctor_id, first_name, last_name, email_address, specialization_name, department_name,
total_appointments, completed_appointments, cancelled_appointments, appointment_completion_rate,
procedures_performed := COALESCE(dp.procedures_performed, 0),
avg_procedure_cost := COALESCE(dp.avg_procedure_cost, 0),
total_procedure_revenue := COALESCE(dp.total_procedure_revenue, 0),
procedure_documentation_rate := COALESCE(dp.procedure_documentation_rate, 0),
lab_tests_ordered := COALESCE(dl.lab_tests_ordered, 0),
avg_test_cost := COALESCE(dl.avg_test_cost, 0),
total_lab_revenue := COALESCE(dl.total_lab_revenue, 0),
lab_result_availability_rate := COALESCE(dl.lab_result_availability_rate, 0),
referrals_made := COALESCE(dr.referrals_made, 0),
referral_followup_rate := COALESCE(dr.referral_followup_rate, 0),
total_unique_patients := COALESCE(dup.total_unique_patients, 0),
performance_score :=
ROUND(
COALESCE(appointment_completion_rate, 0) * 0.20
+ COALESCE(dp.procedure_documentation_rate, 0) * 0.20
+ COALESCE(dl.lab_result_availability_rate, 0) * 0.15
+ COALESCE(dr.referral_followup_rate, 0) * 0.15
+ LEAST(COALESCE(dp.procedures_performed, 0), 50) * 0.30
+ LEAST(COALESCE(dup.total_unique_patients, 0), 100) * 0.20
, 2)
(
(
(
(DoctorAppointments da
⟕ (da.doctor_id = dp.doctor_id) DoctorProcedures dp)
⟕ (da.doctor_id = dl.doctor_id) DoctorLabTests dl
)
⟕ (da.doctor_id = dr.doctor_id) DoctorReferrals dr
)
⟕ (da.doctor_id = dup.doctor_id) DoctorUniquePatients dup
)
RankedDoctors ←
rank_dense
performance_rank := ORDER BY performance_score DESC
(DoctorScores)
Result ←
τ performance_rank ASC, last_name ASC, first_name ASC
(RankedDoctors)
Patient health risk assessment
This report identifies which patients carry the highest clinical risk, for proactive outreach and care-coordination purposes, combining:
- Chronic condition burden is the number of distinct diagnoses on file, capped at 10 in scoring so one outlier patient can't dominate the whole composite score, plus the count of severe symptoms.
- Allergy severity reflects the worst allergy severity level on file for the patient.
- A direct medication safety flag counts how many of the patient's currently prescribed medications are linked, via
allergy_prescription_restrictions, to one of their documented allergies. This is the strongest single signal in the score, worth 10 points per conflict. - Polypharmacy tier reflects how many distinct medications the patient currently has on record, bucketed into
LOW_POLYPHARMACY,MODERATE_POLYPHARMACY, orHIGH_POLYPHARMACY. - Appointment adherence covers the completion rate and days since the last appointment, within the lookback window set by the
:lookback_monthsparameter. - Referral burden is the number of referrals in the last 6 months.
- Age acts as a standard clinical risk modifier, with patients over 65 receiving additional points.
These combine into a risk_score, bucketed into CRITICAL/HIGH/MODERATE/LOW, and ranked with DENSE_RANK().
SQL
WITH params AS (
SELECT
CAST(:lookback_months AS INTEGER) AS lookback_months,
CURRENT_DATE - (CAST(:lookback_months AS INTEGER) * INTERVAL '1 month') AS risk_assessment_start
),
patient_records AS (
SELECT p.patient_id, mr.record_id
FROM patients p
LEFT JOIN medical_records mr ON mr.patient_id = p.patient_id
),
patient_chronic_conditions AS (
SELECT
p.patient_id, p.first_name, p.last_name, p.embg,
EXTRACT(YEAR FROM AGE(CURRENT_DATE, p.date_of_birth))::int AS age,
COUNT(DISTINCT d.diagnosis_id) AS chronic_diagnoses_count,
COUNT(DISTINCT CASE WHEN UPPER(mrs.severity) = 'SEVERE' THEN mrs.symptom_id END) AS severe_symptoms_count
FROM patients p
LEFT JOIN diagnosis d ON d.patient_id = p.patient_id
LEFT JOIN patient_records pr ON pr.patient_id = p.patient_id
LEFT JOIN medical_record_symptoms mrs ON mrs.record_id = pr.record_id
GROUP BY p.patient_id, p.first_name, p.last_name, p.embg, p.date_of_birth
),
patient_medication_profile AS (
SELECT
p.patient_id,
COUNT(DISTINCT pmr.prescription_id) AS current_medications,
CASE
WHEN COUNT(DISTINCT pmr.prescription_id) >= 5 THEN 'HIGH_POLYPHARMACY'
WHEN COUNT(DISTINCT pmr.prescription_id) >= 3 THEN 'MODERATE_POLYPHARMACY'
ELSE 'LOW_POLYPHARMACY'
END AS polypharmacy_status,
COUNT(DISTINCT pa.allergy_id) AS allergy_count,
CASE
WHEN COUNT(DISTINCT CASE WHEN a.allergy_severity = 'CRITICAL' THEN pa.allergy_id END) > 0 THEN 'CRITICAL'
WHEN COUNT(DISTINCT CASE WHEN a.allergy_severity = 'HIGH' THEN pa.allergy_id END) > 0 THEN 'HIGH'
WHEN COUNT(DISTINCT pa.allergy_id) > 0 THEN 'MEDIUM'
ELSE 'LOW'
END AS max_allergy_severity,
COUNT(DISTINCT restr.restriction_id) FILTER (WHERE pmr2.prescription_id IS NOT NULL)
AS conflicting_restrictions_on_current_meds
FROM patients p
LEFT JOIN patient_records pr ON pr.patient_id = p.patient_id
LEFT JOIN prescription_medical_records pmr ON pmr.record_id = pr.record_id
LEFT JOIN patient_allergies pa ON pa.patient_id = p.patient_id
LEFT JOIN allergies a ON a.allergy_id = pa.allergy_id
LEFT JOIN allergy_prescription_restrictions apr ON apr.allergy_id = a.allergy_id
LEFT JOIN prescription_restriction restr ON restr.restriction_id = apr.restriction_id
LEFT JOIN prescription_medical_records pmr2
ON pmr2.record_id = pr.record_id AND pmr2.prescription_id = restr.prescription_id
GROUP BY p.patient_id
),
patient_activity AS (
SELECT
p.patient_id,
ROUND(100.0 * COUNT(a.appointment_id) FILTER (WHERE a.status = 'COMPLETED')
/ NULLIF(COUNT(a.appointment_id), 0), 2) AS appointment_completion_rate,
CURRENT_DATE - MAX(a.appointment_date) AS days_since_last_appointment
FROM patients p
LEFT JOIN appointments a
ON a.patient_id = p.patient_id
AND a.appointment_date >= (SELECT risk_assessment_start FROM params)
GROUP BY p.patient_id
),
patient_referrals AS (
SELECT
p.patient_id,
COUNT(DISTINCT CASE WHEN r.referral_date > CURRENT_DATE - INTERVAL '6 months' THEN r.referral_id END)
AS referrals_last_6_months
FROM patients p
LEFT JOIN patient_records pr ON pr.patient_id = p.patient_id
LEFT JOIN referrals r ON r.record_id = pr.record_id
GROUP BY p.patient_id
),
patient_risk_scores AS (
SELECT
pcc.patient_id, pcc.first_name, pcc.last_name, pcc.embg, pcc.age,
pcc.chronic_diagnoses_count, pcc.severe_symptoms_count,
COALESCE(pmp.allergy_count, 0) AS allergy_count,
COALESCE(pmp.max_allergy_severity, 'LOW') AS max_allergy_severity,
COALESCE(pmp.conflicting_restrictions_on_current_meds, 0) AS conflicting_restrictions_on_current_meds,
COALESCE(pmp.current_medications, 0) AS current_medications,
COALESCE(pmp.polypharmacy_status, 'LOW_POLYPHARMACY') AS polypharmacy_status,
COALESCE(pa.appointment_completion_rate, 100) AS appointment_completion_rate,
COALESCE(pa.days_since_last_appointment, 9999) AS days_since_last_appointment,
COALESCE(pr.referrals_last_6_months, 0) AS referrals_last_6_months,
ROUND(
LEAST(COALESCE(pcc.chronic_diagnoses_count, 0), 10) * 3
+ COALESCE(pcc.severe_symptoms_count, 0) * 4
+ CASE COALESCE(pmp.max_allergy_severity, 'LOW')
WHEN 'CRITICAL' THEN 20 WHEN 'HIGH' THEN 10 WHEN 'MEDIUM' THEN 5 ELSE 0 END
+ CASE COALESCE(pmp.polypharmacy_status, 'LOW_POLYPHARMACY')
WHEN 'HIGH_POLYPHARMACY' THEN 15 WHEN 'MODERATE_POLYPHARMACY' THEN 8 ELSE 0 END
+ COALESCE(pmp.conflicting_restrictions_on_current_meds, 0) * 10
+ (100 - COALESCE(pa.appointment_completion_rate, 100)) * 0.2
+ CASE WHEN COALESCE(pa.days_since_last_appointment, 9999) > 180 THEN 10 ELSE 0 END
+ COALESCE(pr.referrals_last_6_months, 0) * 2
+ CASE WHEN pcc.age > 65 THEN 10 ELSE 0 END
, 2) AS risk_score
FROM patient_chronic_conditions pcc
LEFT JOIN patient_medication_profile pmp ON pmp.patient_id = pcc.patient_id
LEFT JOIN patient_activity pa ON pa.patient_id = pcc.patient_id
LEFT JOIN patient_referrals pr ON pr.patient_id = pcc.patient_id
)
SELECT
patient_id, first_name, last_name, embg, age,
chronic_diagnoses_count, severe_symptoms_count,
allergy_count, max_allergy_severity, conflicting_restrictions_on_current_meds,
current_medications, polypharmacy_status,
appointment_completion_rate, days_since_last_appointment,
referrals_last_6_months,
risk_score,
CASE
WHEN risk_score > 75 THEN 'CRITICAL'
WHEN risk_score > 50 THEN 'HIGH'
WHEN risk_score > 25 THEN 'MODERATE'
ELSE 'LOW'
END AS risk_category,
DENSE_RANK() OVER (ORDER BY risk_score DESC) AS risk_rank
FROM patient_risk_scores
WHERE chronic_diagnoses_count > 0 OR allergy_count > 0 OR current_medications > 0
ORDER BY risk_rank, last_name, first_name;
Relational Algebra
Params ← {(lookback_months, risk_assessment_start)}
PatientRecords ←
π_{patient_id, record_id}
(patients p ⟕ (p.patient_id = mr.patient_id) medical_records mr)
PatientChronicConditions ←
γ
patient_id := p.patient_id; first_name := p.first_name; last_name := p.last_name; embg := p.embg;
age := YEAR(AGE(CURRENT_DATE, p.date_of_birth));
chronic_diagnoses_count := COUNT_DISTINCT(d.diagnosis_id);
severe_symptoms_count := COUNT_DISTINCT(CASE WHEN UPPER(mrs.severity) = 'SEVERE' THEN mrs.symptom_id END)
(
(
(patients p ⟕ (p.patient_id = d.patient_id) diagnosis d)
⟕ (p.patient_id = pr.patient_id) PatientRecords pr
)
⟕ (pr.record_id = mrs.record_id) medical_record_symptoms mrs
)
PatientMedicationProfile ←
γ
patient_id := p.patient_id;
current_medications := COUNT_DISTINCT(pmr.prescription_id);
polypharmacy_status :=
CASE WHEN COUNT_DISTINCT(pmr.prescription_id) ≥ 5 THEN 'HIGH_POLYPHARMACY'
WHEN COUNT_DISTINCT(pmr.prescription_id) ≥ 3 THEN 'MODERATE_POLYPHARMACY'
ELSE 'LOW_POLYPHARMACY' END;
allergy_count := COUNT_DISTINCT(pa.allergy_id);
max_allergy_severity :=
CASE WHEN COUNT_DISTINCT(CASE WHEN a.allergy_severity = 'CRITICAL' THEN pa.allergy_id END) > 0 THEN 'CRITICAL'
WHEN COUNT_DISTINCT(CASE WHEN a.allergy_severity = 'HIGH' THEN pa.allergy_id END) > 0 THEN 'HIGH'
WHEN COUNT_DISTINCT(pa.allergy_id) > 0 THEN 'MEDIUM' ELSE 'LOW' END;
conflicting_restrictions_on_current_meds :=
COUNT_DISTINCT(restr.restriction_id) FILTER (pmr2.prescription_id IS NOT NULL)
(
(
(
(
(
(patients p ⟕ (p.patient_id = pr.patient_id) PatientRecords pr)
⟕ (pr.record_id = pmr.record_id) prescription_medical_records pmr
)
⟕ (p.patient_id = pa.patient_id) patient_allergies pa
)
⟕ (pa.allergy_id = a.allergy_id) allergies a
)
⟕ (a.allergy_id = apr.allergy_id) allergy_prescription_restrictions apr
)
⟕ (apr.restriction_id = restr.restriction_id) prescription_restriction restr
⟕ (pr.record_id = pmr2.record_id ∧ pmr2.prescription_id = restr.prescription_id) prescription_medical_records pmr2
)
FilteredAppointments ← σ (a.appointment_date ≥ p.risk_assessment_start) (appointments a × Params p)
PatientActivity ←
γ
patient_id := p.patient_id;
appointment_completion_rate := ROUND(100.0 * COUNT(fa.appointment_id) FILTER (fa.status = 'COMPLETED') / COUNT(fa.appointment_id), 2);
days_since_last_appointment := CURRENT_DATE - MAX(fa.appointment_date)
(
patients p ⟕ (p.patient_id = fa.patient_id) FilteredAppointments fa
)
PatientReferrals ←
γ
patient_id := p.patient_id;
referrals_last_6_months := COUNT_DISTINCT(CASE WHEN r.referral_date > CURRENT_DATE - 6 MONTHS THEN r.referral_id END)
(
(patients p ⟕ (p.patient_id = pr.patient_id) PatientRecords pr)
⟕ (pr.record_id = r.record_id) referrals r
)
PatientRiskScores ←
π
patient_id, first_name, last_name, embg, age, chronic_diagnoses_count, severe_symptoms_count,
allergy_count := COALESCE(pmp.allergy_count, 0),
max_allergy_severity := COALESCE(pmp.max_allergy_severity, 'LOW'),
conflicting_restrictions_on_current_meds := COALESCE(pmp.conflicting_restrictions_on_current_meds, 0),
current_medications := COALESCE(pmp.current_medications, 0),
polypharmacy_status := COALESCE(pmp.polypharmacy_status, 'LOW_POLYPHARMACY'),
appointment_completion_rate := COALESCE(pa.appointment_completion_rate, 100),
days_since_last_appointment := COALESCE(pa.days_since_last_appointment, 9999),
referrals_last_6_months := COALESCE(pr.referrals_last_6_months, 0),
risk_score :=
ROUND(
LEAST(COALESCE(chronic_diagnoses_count, 0), 10) * 3
+ COALESCE(severe_symptoms_count, 0) * 4
+ CASE COALESCE(pmp.max_allergy_severity, 'LOW')
WHEN 'CRITICAL' THEN 20 WHEN 'HIGH' THEN 10 WHEN 'MEDIUM' THEN 5 ELSE 0 END
+ CASE COALESCE(pmp.polypharmacy_status, 'LOW_POLYPHARMACY')
WHEN 'HIGH_POLYPHARMACY' THEN 15 WHEN 'MODERATE_POLYPHARMACY' THEN 8 ELSE 0 END
+ COALESCE(pmp.conflicting_restrictions_on_current_meds, 0) * 10
+ (100 - COALESCE(pa.appointment_completion_rate, 100)) * 0.2
+ CASE WHEN COALESCE(pa.days_since_last_appointment, 9999) > 180 THEN 10 ELSE 0 END
+ COALESCE(pr.referrals_last_6_months, 0) * 2
+ CASE WHEN age > 65 THEN 10 ELSE 0 END
, 2)
(
(
(PatientChronicConditions pcc
⟕ (pcc.patient_id = pmp.patient_id) PatientMedicationProfile pmp)
⟕ (pcc.patient_id = pa.patient_id) PatientActivity pa
)
⟕ (pcc.patient_id = pr.patient_id) PatientReferrals pr
)
RiskCategorized ←
π
*,
risk_category :=
CASE WHEN risk_score > 75 THEN 'CRITICAL'
WHEN risk_score > 50 THEN 'HIGH'
WHEN risk_score > 25 THEN 'MODERATE'
ELSE 'LOW' END
(PatientRiskScores)
FilteredPatients ←
σ (chronic_diagnoses_count > 0 ∨ allergy_count > 0 ∨ current_medications > 0)
(RiskCategorized)
RankedPatients ←
rank_dense
risk_rank := ORDER BY risk_score DESC
(FilteredPatients)
Result ←
τ risk_rank ASC, last_name ASC, first_name ASC
(RankedPatients)
Last modified
12 days ago
Last modified on 08/29/26 13:43:39
Note:
See TracWiki
for help on using the wiki.
