Changes between Version 1 and Version 2 of AdvancedReports


Ignore:
Timestamp:
09/23/26 17:18:11 (11 hours ago)
Author:
236021
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • AdvancedReports

    v1 v2  
    66== Doctor performance analytics ==
    77
    8 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:
    9 
    10 * Appointment volume and completion rate.
    11 * Procedure volume, revenue, and how often a performed procedure has a documented result on file.
    12 * Lab test volume, revenue, and how often an ordered test type has a result available.
    13 * Referral volume and whether referred patients actually followed up with the receiving doctor.
     8This report identifies which doctors are performing best over a given date range (`report_start`–`report_end`, set in the `params` CTE). Every doctor with at least one appointment, performed procedure, ordered lab test, or referral in the range is included; doctors with no activity in the range are left out of the ranking. For each doctor the report shows:
     9
     10* Appointment volume, split into completed, cancelled, and open (`SCHEDULED` or `IN_PROGRESS`) appointments, and the completion rate. The completion rate only considers resolved appointments, `COMPLETED` / (`COMPLETED` + `CANCELLED`), so open appointments, including future ones, do not lower it.
     11* Procedure volume, revenue, and the procedure documentation rate: the share of the doctor's performed procedures that have a result recorded in `procedure_results`.
     12* Lab test volume, revenue, and the lab result availability rate: the share of ordered test types that have at least one result in `lab_results`.
     13* Referral volume and the referral follow-up rate is the share of referrals where the patient had a non-cancelled appointment with the receiving doctor after the referral date.
    1414* Breadth of care, measured as the number of distinct patients touched across procedures, lab tests, and appointments combined.
    1515
    16 Six factors feed into one weighted performance_score: appointment completion,
    17 procedure documentation, lab result availability, and referral follow-up each
    18 contribute a percentage-based rate, while procedure volume and patient breadth
    19 contribute capped counts (so one very busy doctor can't dominate the score). Doctors
    20 are then ranked with DENSE_RANK(), so tied scores share a rank instead of skipping
    21 numbers.
     16Revenue is calculated at list price, as the number of performed procedures or ordered tests multiplied by the `cost` in `procedures` and `lab_tests`; it does not depend on billing or payment status.
     17
     18Six factors feed into one weighted performance_score: appointment completion (20%),
     19procedure documentation (20%), lab result availability (15%), and referral follow-up
     20(15%) each contribute a percentage-based rate, while procedure volume (0.30 points
     21per procedure, capped at 50) and patient breadth (0.20 points per patient, capped at
     22100) contribute capped counts, so one very busy doctor can't dominate the score.
     23Revenue, lab test volume, and referral volume are reported for context but do not
     24affect the score. A rate that cannot be calculated (for example, a doctor with no lab
     25tests, or with only open appointments) counts as 0. Doctors are then ranked with
     26DENSE_RANK(), so tied scores share a rank instead of skipping numbers.
     27
     28Because `procedure_results` and `lab_results` reference a procedure or test type rather
     29than a specific performed procedure or test, a result counts as documentation for every
     30performance of that procedure or test type.
    2231
    2332
    … …  
    2736WITH params AS (
    2837    SELECT
    29         CAST('2026-05-16' AS DATE) AS report_start,
    30         CAST('2026-08-26' AS DATE) AS report_end
    31 ),
    32 
    33 
     38        CAST('2025-01-01' AS DATE) AS report_start,
     39        CAST('2026-12-31' AS DATE) AS report_end
     40),
    3441documented_procedure_ids AS (
    3542    SELECT DISTINCT procedure_id FROM procedure_results
    … …  
    3845    SELECT DISTINCT test_id FROM lab_results
    3946),
    40 
    4147doctor_appointments AS (
    4248    SELECT
    4349        d.doctor_id, d.first_name, d.last_name, d.email_address,
    4450        ds.specialization_name, dept.department_name,
    45         COUNT(*) AS total_appointments,
    46         COUNT(*) FILTER (WHERE a.status = 'COMPLETED') AS completed_appointments,
    47         COUNT(*) FILTER (WHERE a.status = 'CANCELLED') AS cancelled_appointments,
    48         ROUND(100.0 * COUNT(*) FILTER (WHERE a.status = 'COMPLETED') / NULLIF(COUNT(*), 0), 2) AS appointment_completion_rate
     51        COUNT(a.appointment_id) AS total_appointments,
     52        COUNT(a.appointment_id) FILTER (WHERE a.status = 'COMPLETED') AS completed_appointments,
     53        COUNT(a.appointment_id) FILTER (WHERE a.status = 'CANCELLED') AS cancelled_appointments,
     54        COUNT(a.appointment_id) FILTER (WHERE a.status IN ('SCHEDULED', 'IN_PROGRESS')) AS open_appointments,
     55        ROUND(100.0 * COUNT(a.appointment_id) FILTER (WHERE a.status = 'COMPLETED')
     56            / NULLIF(COUNT(a.appointment_id) FILTER (WHERE a.status IN ('COMPLETED', 'CANCELLED')), 0), 2)
     57            AS appointment_completion_rate
    4958    FROM doctors d
    50         JOIN appointments a ON d.doctor_id = a.doctor_id
    5159        JOIN doctor_specialization ds ON d.specialization_id = ds.specialization_id
    5260        JOIN departments dept ON d.department_id = dept.department_id
    53         JOIN params p ON a.appointment_date >= p.report_start AND a.appointment_date <= p.report_end
     61        CROSS JOIN params p
     62        LEFT JOIN appointments a
     63            ON a.doctor_id = d.doctor_id
     64            AND a.appointment_date >= p.report_start
     65            AND a.appointment_date <= p.report_end
    5466    GROUP BY d.doctor_id, d.first_name, d.last_name, d.email_address, ds.specialization_name, dept.department_name
    5567),
    56 
    5768doctor_procedures AS (
    5869    SELECT
    … …  
    6172        ROUND(AVG(proc.cost)::numeric, 2) AS avg_procedure_cost,
    6273        SUM(proc.cost) AS total_procedure_revenue,
    63         ROUND(100.0 * COUNT(DISTINCT dpi.procedure_id) / NULLIF(COUNT(DISTINCT pp.procedure_id), 0), 2) AS procedure_documentation_rate
     74        ROUND(100.0 * COUNT(dpi.procedure_id) / NULLIF(COUNT(*), 0), 2) AS procedure_documentation_rate
    6475    FROM doctors d
    6576        JOIN performed_procedures pp ON d.doctor_id = pp.doctor_id
    … …  
    6980    GROUP BY d.doctor_id
    7081),
    71 
    7282doctor_lab_tests AS (
    7383    SELECT
    … …  
    8494    GROUP BY d.doctor_id
    8595),
    86 
    8796doctor_referrals AS (
    8897    SELECT
    … …  
    94103              AND fa.patient_id = mr.patient_id
    95104              AND fa.appointment_date > ref.referral_date
     105              AND fa.status <> 'CANCELLED'
    96106        ) THEN ref.referral_id END) / NULLIF(COUNT(DISTINCT ref.referral_id), 0), 2) AS referral_followup_rate
    97107    FROM doctors d
    … …  
    101111    GROUP BY d.doctor_id
    102112),
    103 
    104113doctor_patient_touchpoints AS (
    105114    SELECT pp.doctor_id, pp.patient_id FROM performed_procedures pp
    … …  
    112121        JOIN params p ON a.appointment_date >= p.report_start AND a.appointment_date <= p.report_end
    113122),
    114 
    115123doctor_unique_patients AS (
    116124    SELECT doctor_id, COUNT(DISTINCT patient_id) AS total_unique_patients
    … …  
    118126    GROUP BY doctor_id
    119127),
    120 
    121128doctor_scores AS (
    122129    SELECT
    123130        da.doctor_id, da.first_name, da.last_name, da.email_address,
    124131        da.specialization_name, da.department_name,
    125         da.total_appointments, da.completed_appointments, da.cancelled_appointments, da.appointment_completion_rate,
     132        da.total_appointments, da.completed_appointments, da.cancelled_appointments, da.open_appointments,
     133        COALESCE(da.appointment_completion_rate, 0) AS appointment_completion_rate,
    126134        COALESCE(dp.procedures_performed, 0) AS procedures_performed,
    127135        COALESCE(dp.avg_procedure_cost, 0) AS avg_procedure_cost,
    … …  
    149157        LEFT JOIN doctor_unique_patients dup ON da.doctor_id = dup.doctor_id
    150158)
    151 
    152159SELECT *,
    153160    DENSE_RANK() OVER (ORDER BY performance_score DESC) AS performance_rank
    154161FROM doctor_scores
     162WHERE total_appointments > 0
     163   OR procedures_performed > 0
     164   OR lab_tests_ordered > 0
     165   OR referrals_made > 0
    155166ORDER BY performance_rank, last_name, first_name;
    156167}}}
    … …  
    169180  email_address := d.email_address; specialization_name := ds.specialization_name;
    170181  department_name := dept.department_name;
    171   total_appointments := COUNT(*);
    172   completed_appointments := COUNT(*) FILTER (a.status = 'COMPLETED');
    173   cancelled_appointments := COUNT(*) FILTER (a.status = 'CANCELLED');
    174   appointment_completion_rate := ROUND(100.0 * COUNT(*) FILTER (a.status = 'COMPLETED') / COUNT(*), 2)
    175 (
    176   σ (a.appointment_date ≥ p.report_start ∧ a.appointment_date ≤ p.report_end)
     182  total_appointments := COUNT(a.appointment_id);
     183  completed_appointments := COUNT(a.appointment_id) FILTER (a.status = 'COMPLETED');
     184  cancelled_appointments := COUNT(a.appointment_id) FILTER (a.status = 'CANCELLED');
     185  open_appointments := COUNT(a.appointment_id) FILTER (a.status ∈ {'SCHEDULED', 'IN_PROGRESS'});
     186  appointment_completion_rate := ROUND(100.0 * COUNT(a.appointment_id) FILTER (a.status = 'COMPLETED')
     187                                 / COUNT(a.appointment_id) FILTER (a.status ∈ {'COMPLETED', 'CANCELLED'}), 2)
     188(
    177189  (
    178190    (
    179       (doctors d ⨝ (d.doctor_id = a.doctor_id) appointments a)
    180       ⨝ (d.specialization_id = ds.specialization_id) doctor_specialization ds
     191      (doctors d ⨝ (d.specialization_id = ds.specialization_id) doctor_specialization ds)
     192      ⨝ (d.department_id = dept.department_id) departments dept
    181193    )
    182     ⨝ (d.department_id = dept.department_id) departments dept
    183194    × Params p
    184195  )
     196  ⟕ (d.doctor_id = a.doctor_id ∧ a.appointment_date ≥ p.report_start ∧ a.appointment_date ≤ p.report_end) appointments a
    185197)
    186198
    … …  
    191203  avg_procedure_cost := ROUND(AVG(proc.cost), 2);
    192204  total_procedure_revenue := SUM(proc.cost);
    193   procedure_documentation_rate := ROUND(100.0 * COUNT_DISTINCT(dpi.procedure_id) / COUNT_DISTINCT(pp.procedure_id), 2)
     205  procedure_documentation_rate := ROUND(100.0 * COUNT(dpi.procedure_id) / COUNT(*), 2)
    194206(
    195207  σ (pp.procedure_date ≥ p.report_start ∧ pp.procedure_date ≤ p.report_end)
    196208  (
    197209    (
    198       (doctors d ⨝ (d.doctor_id = pp.doctor_id) performed_procedures pp)
    199       ⨝ (pp.procedure_id = proc.procedure_id) procedures proc
     210      (
     211        (doctors d ⨝ (d.doctor_id = pp.doctor_id) performed_procedures pp)
     212        ⨝ (pp.procedure_id = proc.procedure_id) procedures proc
     213      )
     214      ⟕ (pp.procedure_id = dpi.procedure_id) DocumentedProcedureIds dpi
    200215    )
    201     ⟕ (pp.procedure_id = dpi.procedure_id) DocumentedProcedureIds dpi
    202216    × Params p
    203217  )
    … …  
    215229  (
    216230    (
    217       (doctors d ⨝ (d.doctor_id = plt.doctor_id) performed_lab_tests plt)
    218       ⨝ (plt.test_id = lt.test_id) lab_tests lt
     231      (
     232        (doctors d ⨝ (d.doctor_id = plt.doctor_id) performed_lab_tests plt)
     233        ⨝ (plt.test_id = lt.test_id) lab_tests lt
     234      )
     235      ⟕ (plt.test_id = dti.test_id) DocumentedTestIds dti
    219236    )
    220     ⟕ (plt.test_id = dti.test_id) DocumentedTestIds dti
    221237    × Params p
    222238  )
    … …  
    226242π_{referral_id}
    227243(
    228   σ (fa.doctor_id = ref.to_doctor_id ∧ fa.patient_id = mr.patient_id ∧ fa.appointment_date > ref.referral_date)
     244  σ (fa.doctor_id = ref.to_doctor_id ∧ fa.patient_id = mr.patient_id
     245     ∧ fa.appointment_date > ref.referral_date ∧ fa.status ≠ 'CANCELLED')
    229246  (
    230247    (referrals ref ⨝ (ref.record_id = mr.record_id) medical_records mr)
    … …  
    242259  (
    243260    (
    244       (doctors d ⨝ (d.doctor_id = ref.from_doctor_id) referrals ref)
    245       ⨝ (ref.record_id = mr.record_id) medical_records mr
     261      (
     262        (doctors d ⨝ (d.doctor_id = ref.from_doctor_id) referrals ref)
     263        ⨝ (ref.record_id = mr.record_id) medical_records mr
     264      )
     265      ⟕ (ref.referral_id = fur.referral_id) FollowedUpReferrals fur
    246266    )
    247     ⟕ (ref.referral_id = fur.referral_id) FollowedUpReferrals fur
    248267    × Params p
    249268  )
    … …  
    273292π
    274293  doctor_id, first_name, last_name, email_address, specialization_name, department_name,
    275   total_appointments, completed_appointments, cancelled_appointments, appointment_completion_rate,
     294  total_appointments, completed_appointments, cancelled_appointments, open_appointments,
     295  appointment_completion_rate := COALESCE(da.appointment_completion_rate, 0),
    276296  procedures_performed := COALESCE(dp.procedures_performed, 0),
    277297  avg_procedure_cost := COALESCE(dp.avg_procedure_cost, 0),
    … …  
    287307  performance_score :=
    288308    ROUND(
    289       COALESCE(appointment_completion_rate, 0) * 0.20
     309      COALESCE(da.appointment_completion_rate, 0) * 0.20
    290310      + COALESCE(dp.procedure_documentation_rate, 0) * 0.20
    291311      + COALESCE(dl.lab_result_availability_rate, 0) * 0.15
    … …  
    306326)
    307327
     328ActiveDoctors ←
     329σ (total_appointments > 0 ∨ procedures_performed > 0 ∨ lab_tests_ordered > 0 ∨ referrals_made > 0)
     330(DoctorScores)
     331
    308332RankedDoctors ←
    309333rank_dense
    310334  performance_rank := ORDER BY performance_score DESC
    311 (DoctorScores)
     335(ActiveDoctors)
    312336
    313337Result ←
    … …  
    324348
    325349* 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.
    326 * Allergy severity reflects the worst allergy severity level on file for the patient.
    327 * 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.
    328 * Polypharmacy tier reflects how many distinct medications the patient currently has on record, bucketed into `LOW_POLYPHARMACY`, `MODERATE_POLYPHARMACY`, or `HIGH_POLYPHARMACY`.
    329 * Appointment adherence covers the completion rate and days since the last appointment, within the lookback window set by the `:lookback_months` parameter.
     350* Allergy severity reflects the worst allergy severity level on file for the patient (`NONE` if the patient has no documented allergies).
     351* A direct medication safety flag counts how many of the patient's prescribed medications are linked, via `allergy_prescription_restrictions`, to one of their documented allergies. It is one of the most heavily weighted signals in the score, worth 10 points per conflicting medication, and is not capped.
     352* Polypharmacy tier reflects how many distinct medications the patient has on record, bucketed into `LOW_POLYPHARMACY`, `MODERATE_POLYPHARMACY`, or `HIGH_POLYPHARMACY`. Prescription records carry no dates or active status, so every medication ever prescribed to the patient is treated as current.
     353* Appointment adherence covers the completion rate of past appointments within the lookback window set by the `:lookback_months` parameter (future scheduled appointments are excluded), and the number of days since the patient's last completed appointment across their full history. Patients with no completed appointment, or none in the last 180 days, receive additional points.
    330354* Referral burden is the number of referrals in the last 6 months.
    331355* Age acts as a standard clinical risk modifier, with patients over 65 receiving additional points.
    332356
     357Only the diagnosis count is capped; severe symptoms, medication conflicts, and referrals add points without an upper limit.
    333358These combine into a `risk_score`, bucketed into `CRITICAL`/`HIGH`/`MODERATE`/`LOW`, and ranked with DENSE_RANK().
    334359
    … …  
    341366        CURRENT_DATE - (CAST(:lookback_months AS INTEGER) * INTERVAL '1 month') AS risk_assessment_start
    342367),
    343 
    344368patient_records AS (
    345369    SELECT p.patient_id, mr.record_id
    … …  
    347371        LEFT JOIN medical_records mr ON mr.patient_id = p.patient_id
    348372),
    349 
    350 
    351373patient_chronic_conditions AS (
    352374    SELECT
    … …  
    361383    GROUP BY p.patient_id, p.first_name, p.last_name, p.embg, p.date_of_birth
    362384),
    363 
    364 
    365385patient_medication_profile AS (
    366386    SELECT
    … …  
    376396            WHEN COUNT(DISTINCT CASE WHEN a.allergy_severity = 'CRITICAL' THEN pa.allergy_id END) > 0 THEN 'CRITICAL'
    377397            WHEN COUNT(DISTINCT CASE WHEN a.allergy_severity = 'HIGH' THEN pa.allergy_id END) > 0 THEN 'HIGH'
    378             WHEN COUNT(DISTINCT pa.allergy_id) > 0 THEN 'MEDIUM'
    379             ELSE 'LOW'
     398            WHEN COUNT(DISTINCT CASE WHEN a.allergy_severity = 'MEDIUM' THEN pa.allergy_id END) > 0 THEN 'MEDIUM'
     399            WHEN COUNT(DISTINCT pa.allergy_id) > 0 THEN 'LOW'
     400            ELSE 'NONE'
    380401        END AS max_allergy_severity,
    381         COUNT(DISTINCT restr.restriction_id) FILTER (WHERE pmr2.prescription_id IS NOT NULL)
    382             AS conflicting_restrictions_on_current_meds
     402        COUNT(DISTINCT pmr2.prescription_id) AS conflicting_medications
    383403    FROM patients p
    384404        LEFT JOIN patient_records pr ON pr.patient_id = p.patient_id
    … …  
    392412    GROUP BY p.patient_id
    393413),
    394 
    395414patient_activity AS (
    396415    SELECT
    397416        p.patient_id,
    398417        ROUND(100.0 * COUNT(a.appointment_id) FILTER (WHERE a.status = 'COMPLETED')
    399             / NULLIF(COUNT(a.appointment_id), 0), 2) AS appointment_completion_rate,
    400         CURRENT_DATE - MAX(a.appointment_date) AS days_since_last_appointment
     418            / NULLIF(COUNT(a.appointment_id), 0), 2) AS appointment_completion_rate
    401419    FROM patients p
     420        CROSS JOIN params prm
    402421        LEFT JOIN appointments a
    403422            ON a.patient_id = p.patient_id
    404             AND a.appointment_date >= (SELECT risk_assessment_start FROM params)
     423            AND a.appointment_date >= prm.risk_assessment_start
     424            AND a.appointment_date <= CURRENT_DATE
    405425    GROUP BY p.patient_id
    406426),
    407 
     427patient_last_visit AS (
     428    SELECT
     429        patient_id,
     430        CURRENT_DATE - MAX(appointment_date) AS days_since_last_appointment
     431    FROM appointments
     432    WHERE status = 'COMPLETED'
     433      AND appointment_date <= CURRENT_DATE
     434    GROUP BY patient_id
     435),
    408436patient_referrals AS (
    409437    SELECT
    … …  
    416444    GROUP BY p.patient_id
    417445),
    418 
    419446patient_risk_scores AS (
    420447    SELECT
    … …  
    422449        pcc.chronic_diagnoses_count, pcc.severe_symptoms_count,
    423450        COALESCE(pmp.allergy_count, 0) AS allergy_count,
    424         COALESCE(pmp.max_allergy_severity, 'LOW') AS max_allergy_severity,
    425         COALESCE(pmp.conflicting_restrictions_on_current_meds, 0) AS conflicting_restrictions_on_current_meds,
     451        COALESCE(pmp.max_allergy_severity, 'NONE') AS max_allergy_severity,
     452        COALESCE(pmp.conflicting_medications, 0) AS conflicting_medications,
    426453        COALESCE(pmp.current_medications, 0) AS current_medications,
    427454        COALESCE(pmp.polypharmacy_status, 'LOW_POLYPHARMACY') AS polypharmacy_status,
    428455        COALESCE(pa.appointment_completion_rate, 100) AS appointment_completion_rate,
    429         COALESCE(pa.days_since_last_appointment, 9999) AS days_since_last_appointment,
     456        COALESCE(plv.days_since_last_appointment, 9999) AS days_since_last_appointment,
    430457        COALESCE(pr.referrals_last_6_months, 0) AS referrals_last_6_months,
    431458        ROUND(
    432459            LEAST(COALESCE(pcc.chronic_diagnoses_count, 0), 10) * 3
    433460            + COALESCE(pcc.severe_symptoms_count, 0) * 4
    434             + CASE COALESCE(pmp.max_allergy_severity, 'LOW')
     461            + CASE COALESCE(pmp.max_allergy_severity, 'NONE')
    435462                  WHEN 'CRITICAL' THEN 20 WHEN 'HIGH' THEN 10 WHEN 'MEDIUM' THEN 5 ELSE 0 END
    436463            + CASE COALESCE(pmp.polypharmacy_status, 'LOW_POLYPHARMACY')
    437464                  WHEN 'HIGH_POLYPHARMACY' THEN 15 WHEN 'MODERATE_POLYPHARMACY' THEN 8 ELSE 0 END
    438             + COALESCE(pmp.conflicting_restrictions_on_current_meds, 0) * 10
     465            + COALESCE(pmp.conflicting_medications, 0) * 10
    439466            + (100 - COALESCE(pa.appointment_completion_rate, 100)) * 0.2
    440             + CASE WHEN COALESCE(pa.days_since_last_appointment, 9999) > 180 THEN 10 ELSE 0 END
     467            + CASE WHEN COALESCE(plv.days_since_last_appointment, 9999) > 180 THEN 10 ELSE 0 END
    441468            + COALESCE(pr.referrals_last_6_months, 0) * 2
    442469            + CASE WHEN pcc.age > 65 THEN 10 ELSE 0 END
    … …  
    445472        LEFT JOIN patient_medication_profile pmp ON pmp.patient_id = pcc.patient_id
    446473        LEFT JOIN patient_activity pa ON pa.patient_id = pcc.patient_id
     474        LEFT JOIN patient_last_visit plv ON plv.patient_id = pcc.patient_id
    447475        LEFT JOIN patient_referrals pr ON pr.patient_id = pcc.patient_id
    448476)
    449 
    450477SELECT
    451478    patient_id, first_name, last_name, embg, age,
    452479    chronic_diagnoses_count, severe_symptoms_count,
    453     allergy_count, max_allergy_severity, conflicting_restrictions_on_current_meds,
     480    allergy_count, max_allergy_severity, conflicting_medications,
    454481    current_medications, polypharmacy_status,
    455482    appointment_completion_rate, days_since_last_appointment,
    … …  
    503530    CASE WHEN COUNT_DISTINCT(CASE WHEN a.allergy_severity = 'CRITICAL' THEN pa.allergy_id END) > 0 THEN 'CRITICAL'
    504531         WHEN COUNT_DISTINCT(CASE WHEN a.allergy_severity = 'HIGH' THEN pa.allergy_id END) > 0 THEN 'HIGH'
    505          WHEN COUNT_DISTINCT(pa.allergy_id) > 0 THEN 'MEDIUM' ELSE 'LOW' END;
    506   conflicting_restrictions_on_current_meds :=
    507     COUNT_DISTINCT(restr.restriction_id) FILTER (pmr2.prescription_id IS NOT NULL)
     532         WHEN COUNT_DISTINCT(CASE WHEN a.allergy_severity = 'MEDIUM' THEN pa.allergy_id END) > 0 THEN 'MEDIUM'
     533         WHEN COUNT_DISTINCT(pa.allergy_id) > 0 THEN 'LOW'
     534         ELSE 'NONE' END;
     535  conflicting_medications := COUNT_DISTINCT(pmr2.prescription_id)
    508536(
    509537  (
    … …  
    511539      (
    512540        (
    513           (patients p ⟕ (p.patient_id = pr.patient_id) PatientRecords pr)
    514           ⟕ (pr.record_id = pmr.record_id) prescription_medical_records pmr
     541          (
     542            (patients p ⟕ (p.patient_id = pr.patient_id) PatientRecords pr)
     543            ⟕ (pr.record_id = pmr.record_id) prescription_medical_records pmr
     544          )
     545          ⟕ (p.patient_id = pa.patient_id) patient_allergies pa
    515546        )
    516         ⟕ (p.patient_id = pa.patient_id) patient_allergies pa
     547        ⟕ (pa.allergy_id = a.allergy_id) allergies a
    517548      )
    518       ⟕ (pa.allergy_id = a.allergy_id) allergies a
     549      ⟕ (a.allergy_id = apr.allergy_id) allergy_prescription_restrictions apr
    519550    )
    520     ⟕ (a.allergy_id = apr.allergy_id) allergy_prescription_restrictions apr
    521   )
    522   ⟕ (apr.restriction_id = restr.restriction_id) prescription_restriction restr
     551    ⟕ (apr.restriction_id = restr.restriction_id) prescription_restriction restr
     552  )
    523553  ⟕ (pr.record_id = pmr2.record_id ∧ pmr2.prescription_id = restr.prescription_id) prescription_medical_records pmr2
    524554)
    525555
    526 FilteredAppointments ← σ (a.appointment_date ≥ p.risk_assessment_start) (appointments a × Params p)
     556FilteredAppointments ←
     557π_{a.appointment_id, a.patient_id, a.status}
     558(
     559  σ (a.appointment_date ≥ p.risk_assessment_start ∧ a.appointment_date ≤ CURRENT_DATE)
     560  (appointments a × Params p)
     561)
    527562
    528563PatientActivity ←
    529564γ
    530565  patient_id := p.patient_id;
    531   appointment_completion_rate := ROUND(100.0 * COUNT(fa.appointment_id) FILTER (fa.status = 'COMPLETED') / COUNT(fa.appointment_id), 2);
    532   days_since_last_appointment := CURRENT_DATE - MAX(fa.appointment_date)
     566  appointment_completion_rate := ROUND(100.0 * COUNT(fa.appointment_id) FILTER (fa.status = 'COMPLETED') / COUNT(fa.appointment_id), 2)
    533567(
    534568  patients p ⟕ (p.patient_id = fa.patient_id) FilteredAppointments fa
     569)
     570
     571PatientLastVisit ←
     572γ
     573  patient_id;
     574  days_since_last_appointment := CURRENT_DATE - MAX(appointment_date)
     575(
     576  σ (status = 'COMPLETED' ∧ appointment_date ≤ CURRENT_DATE) (appointments)
    535577)
    536578
    … …  
    548590  patient_id, first_name, last_name, embg, age, chronic_diagnoses_count, severe_symptoms_count,
    549591  allergy_count := COALESCE(pmp.allergy_count, 0),
    550   max_allergy_severity := COALESCE(pmp.max_allergy_severity, 'LOW'),
    551   conflicting_restrictions_on_current_meds := COALESCE(pmp.conflicting_restrictions_on_current_meds, 0),
     592  max_allergy_severity := COALESCE(pmp.max_allergy_severity, 'NONE'),
     593  conflicting_medications := COALESCE(pmp.conflicting_medications, 0),
    552594  current_medications := COALESCE(pmp.current_medications, 0),
    553595  polypharmacy_status := COALESCE(pmp.polypharmacy_status, 'LOW_POLYPHARMACY'),
    554596  appointment_completion_rate := COALESCE(pa.appointment_completion_rate, 100),
    555   days_since_last_appointment := COALESCE(pa.days_since_last_appointment, 9999),
     597  days_since_last_appointment := COALESCE(plv.days_since_last_appointment, 9999),
    556598  referrals_last_6_months := COALESCE(pr.referrals_last_6_months, 0),
    557599  risk_score :=
    … …  
    559601      LEAST(COALESCE(chronic_diagnoses_count, 0), 10) * 3
    560602      + COALESCE(severe_symptoms_count, 0) * 4
    561       + CASE COALESCE(pmp.max_allergy_severity, 'LOW')
     603      + CASE COALESCE(pmp.max_allergy_severity, 'NONE')
    562604             WHEN 'CRITICAL' THEN 20 WHEN 'HIGH' THEN 10 WHEN 'MEDIUM' THEN 5 ELSE 0 END
    563605      + CASE COALESCE(pmp.polypharmacy_status, 'LOW_POLYPHARMACY')
    564606             WHEN 'HIGH_POLYPHARMACY' THEN 15 WHEN 'MODERATE_POLYPHARMACY' THEN 8 ELSE 0 END
    565       + COALESCE(pmp.conflicting_restrictions_on_current_meds, 0) * 10
     607      + COALESCE(pmp.conflicting_medications, 0) * 10
    566608      + (100 - COALESCE(pa.appointment_completion_rate, 100)) * 0.2
    567       + CASE WHEN COALESCE(pa.days_since_last_appointment, 9999) > 180 THEN 10 ELSE 0 END
     609      + CASE WHEN COALESCE(plv.days_since_last_appointment, 9999) > 180 THEN 10 ELSE 0 END
    568610      + COALESCE(pr.referrals_last_6_months, 0) * 2
    569611      + CASE WHEN age > 65 THEN 10 ELSE 0 END
    … …  
    571613(
    572614  (
    573     (PatientChronicConditions pcc
    574     ⟕ (pcc.patient_id = pmp.patient_id) PatientMedicationProfile pmp)
    575     ⟕ (pcc.patient_id = pa.patient_id) PatientActivity pa
     615    (
     616      (PatientChronicConditions pcc
     617      ⟕ (pcc.patient_id = pmp.patient_id) PatientMedicationProfile pmp)
     618      ⟕ (pcc.patient_id = pa.patient_id) PatientActivity pa
     619    )
     620    ⟕ (pcc.patient_id = plv.patient_id) PatientLastVisit plv
    576621  )
    577622  ⟕ (pcc.patient_id = pr.patient_id) PatientReferrals pr