Tough Issues

PCA is one of the highest-risk areas for diversion in any hospital. The closed-loop nature of the pump + pharmacy + waste workflow creates multiple failure points that are hard to detect with standard surveillance.

Why PCAs Are a Diversion Hotspot

• Large drug reservoirs — a single 30mL syringe of hydromorphone 50mcg/mL contains 1.5mg (30 doses). Unused volume is rarely measured precisely.
• Waste witnessing is unreliable — witnessed waste is often signed off without actual observation, especially on night shifts.
• Programming discrepancies — pump logs don't always match pharmacy dispensing records (bolus vs. demand vs. continuous rates).
• Multiple access points — Y-site connectors, tubing ports, and cassette changes all provide opportunities to withdraw drug.
• Used cassette/cuprit bags — residual drug in returned cassettes is rarely assayed or measured. A 10mL discrepancy in a 30mL syringe is 33% — and nearly invisible.

Monitoring Strategy

-- ============================================================
-- PCA DISPENSE vs ADMINISTRATION GAP ANALYSIS
-- ============================================================
-- Purpose: Find PCA encounters where the volume of drug
--   dispensed from pharmacy doesn't match what was
--   administered to the patient + wasted.
-- A gap >5mL means drug left the pharmacy but nobody
--   documented giving it or wasting it.
-- ============================================================

-- Step 1: Build a list of every PCA encounter and how much
--   medication left the pharmacy for it.
WITH pca_dispenses AS (
    SELECT
        encounter_id,
        medication_name,
        SUM(dispensed_quantity_mL) AS total_dispensed,
            -- total mL sent from pharmacy across all syringes
        COUNT(DISTINCT syringe_id) AS syringes_used
            -- how many individual syringes/cassettes were used
    FROM pharmacy_pca_log
    WHERE dispensed_at >= DATEADD(day, -30, GETDATE())
        -- only look at the last 30 days
    GROUP BY encounter_id, medication_name
),

-- Step 2: For the same encounters, get the pump log data --
--   how much was actually delivered to the patient and
--   how much was documented as wasted by the nurse.
pca_admin AS (
    SELECT
        encounter_id,
        medication_name,
        SUM(delivered_volume_mL) AS total_administered,
            -- volume the pump reported delivering
        SUM(waste_volume_mL) AS total_wasted
            -- volume the nurse documented as waste
    FROM pump_administration_log
    WHERE admin_time >= DATEADD(day, -30, GETDATE())
    GROUP BY encounter_id, medication_name
)

-- Step 3: Join the two and calculate the gap.
--   If dispensed > (administered + wasted), something
--   is unaccounted for.
SELECT
    d.encounter_id,
    d.medication_name,
    d.total_dispensed,
    a.total_administered,
    a.total_wasted,
    -- THE KEY METRIC: volume that left pharmacy but was
    -- never documented as given or wasted
    d.total_dispensed - (a.total_administered + a.total_wasted)
        AS unaccounted_volume_mL
FROM pca_dispenses d
JOIN pca_admin a
    ON d.encounter_id = a.encounter_id
    AND d.medication_name = a.medication_name
WHERE d.total_dispensed - (a.total_administered + a.total_wasted) > 5
    -- only flag gaps larger than 5mL (filters out
    -- tubing prime volume and normal variation)
ORDER BY unaccounted_volume_mL DESC;
    -- biggest gaps first -- start investigation there

Patient-owned medications occupy a regulatory gray zone. The hospital accepts custody and responsibility but has limited visibility into the pre-hospital chain of custody. POMs bypass the pharmacy's usual receiving, storage, and dispensing controls.

The Diversion Risk

• No pharmacy receipt verification — the medication's identity and potency are accepted on patient/family representation. A counterfeit or adulterated product could be introduced.
• Outside the ADC chain — POMs are typically stored in the patient's bedside cabinet or personal belongings, not in a medication dispense cabinet. No audit trail of access.
• Administration is inconsistently documented — some EHRs handle POM administration as a free-text note rather than a barcode-scanned MAR transaction.
• Controlled substance POMs — some patients bring in their own controlled medications. If these bypass pharmacy, they're invisible to automated diversion surveillance.

Risk Mitigation Strategies

Most diversion programs focus almost exclusively on controlled substances (C-II through C-V). But non-controlled drugs with abuse potential — diphenhydramine (Benadryl), promethazine (Phenergan), ketorolac (Toradol), lidocaine, and others — are diverted frequently and are almost never caught by controlled-substance surveillance systems.

High-Alert Non-Controlled Drugs

Monitoring Strategy: Start with Dispense → Admin Matching

The single most effective thing you can do for non-controlled diversion detection is to run a dispense-to-administration matching report. Here's how:

-- ============================================================
-- NON-CONTROLLED DRUG: DISPENSE vs ADMIN GAP BY STAFF
-- ============================================================
-- Purpose: Find individual staff members whose documented
--   administration of non-controlled drugs doesn't match
--   what was dispensed. Non-controlled drugs like
--   diphenhydramine and promethazine are frequently
--   diverted but invisible to controlled-substance audits.
--
-- How it works:
--   dispensed_units = what left the ADC
--   administered_units = what was charted in the MAR
--   gap = dispensed - administered (unaccounted drug)
--
-- A high gap + low waste = likely diversion signal.
-- ============================================================
SELECT
    mn.staff_name,
        -- who drew up the medication
    mn.unit,
        -- which unit they work on
    mn.medication_name,
        -- e.g. diphenhydramine, promethazine, ketorolac
    SUM(mn.dispensed_units) - SUM(mn.administered_units) AS gap_units,
        -- THE KEY METRIC: units pulled from ADC but never
        -- charted as administered. Each gap unit could be
        -- one diverted dose.
    COUNT(DISTINCT mn.encounter_id) AS encounters,
        -- how many different patients were involved --
        -- a gap spread across many encounters is more
        -- suspicious than a gap isolated to one patient
    AVG(mn.waste_units) AS avg_waste
        -- average waste per administration. A low waste
        -- rate combined with a high gap suggests the drug
        -- didn't go to the patient AND wasn't wasted.
FROM mar_noncontrolled mn
WHERE mn.medication_name IN ('diphenhydramine', 'promethazine', 'ketorolac')
    -- focus on the most commonly diverted non-controlled drugs
    AND mn.dispensed_at >= DATEADD(month, -1, GETDATE())
    -- look at the last 30 days
GROUP BY mn.staff_name, mn.unit, mn.medication_name
    -- group by person so we can see individual patterns
HAVING SUM(mn.dispensed_units) - SUM(mn.administered_units) > 5
    -- only flag people with >5 unaccounted units
    -- (smaller gaps may be charting errors)
ORDER BY gap_units DESC;
    -- biggest diversion risk at the top

When a patient is discharged or transferred, any unused controlled substance should be returned to pharmacy for destruction. But "returned to pharmacy" is often an honor system — the medication leaves the unit in an envelope and may or may not arrive.

What to Watch For

• Return-to-pharmacy lag time — medication signed out of the ADC but not scanned back into pharmacy inventory within 4 hours.
• Pharmacy returns that don't match dispense records — fewer units returned than expected based on dispense minus administration.
• Returns documented in the EHR but never physically received by pharmacy — paper trail without product.

Overrides are designed for emergencies — when a pharmacist isn't available to verify an order before removal. But they're the single most abused access mode in any medication dispense cabinet. An override removes the pharmacist verification step and the prospective order review. That means no one checks whether the medication is appropriate, dosed correctly, or even ordered for that patient.

The Diversion Mechanism

• Frequent override for the same medication — especially for controlled substances where the patient has an active order. The nurse knows the drug will be there but wants to skip the audit trail.
• Patient override discrepancies — overriding under one patient's name and administering to another. The MAR shows the withdrawal but the patient attribution is wrong.
• Override without follow-up order — a true emergency override should have a corresponding order placed within 24 hours. If it never comes, the drug was removed for non-clinical reasons.
• Night-shift override clustering — when pharmacist coverage is thin, override rates naturally go up. But a single nurse with an override rate 3x their peers warrants investigation.
• Administrative access diversion — nurses with cabinet administrative privileges (manager override, override all, return-to-stock) can remove medications without associating them to any patient encounter. These transactions leave no patient trail at all. Monitor cabinet admin access logs separately from clinical transactions and review who is using elevated privileges and how often.

Monitoring Strategy

-- ============================================================
-- OVERRIDE RATE: STAFF-LEVEL SUMMARY
-- ============================================================
-- Purpose: Identify nurses whose override rate is an outlier
--   compared to their peers on the same unit.
-- An override rate > 30% of all controlled substance removals
--   warrants a closer look.
-- ============================================================
WITH staff_removals AS (
    SELECT
        staff_name,
        unit_name,
        medication_name,
        -- Classify each cabinet transaction by access mode
        SUM(CASE WHEN access_mode = 'override' THEN 1 ELSE 0 END)
            AS override_count,
            -- removals done via override (no pharmacist verify)
        SUM(CASE WHEN access_mode = 'verified' THEN 1 ELSE 0 END)
            AS verified_count,
            -- removals done with pharmacist verification
        SUM(CASE WHEN access_mode = 'admin' THEN 1 ELSE 0 END)
            AS admin_access_count
            -- removals done with admin/elevated privileges
            -- (no patient encounter association)
    FROM cabinet_transaction_log
    WHERE transaction_date >= DATEADD(day, -30, GETDATE())
        AND medication_category = 'controlled'
    GROUP BY staff_name, unit_name, medication_name
)
SELECT
    staff_name,
    unit_name,
    override_count,
    verified_count,
    admin_access_count,
    -- KEY METRIC: what percentage of removals bypassed
    -- the pharmacist?
    ROUND(100.0 * override_count /
        NULLIF(override_count + verified_count + admin_access_count, 0), 1)
        AS override_pct,
    -- How many admin-level (untraceable) removals?
    admin_access_count AS untraceable_removals
FROM staff_removals
WHERE override_count + verified_count + admin_access_count > 10
    -- minimum 10 transactions to be statistically meaningful
ORDER BY override_pct DESC;
    -- highest override rate first

Most diversion monitoring focuses on clinical transactions — nurse removes drug for Patient X, administers, documents waste. But medication dispense cabinets have an administrative access mode that bypasses patient association entirely. Staff with elevated privileges (manager override, return-to-stock, inventory adjustment, override-all) can remove controlled substances without ever linking the transaction to a patient encounter. These transactions are invisible to standard dispense-vs-admin matching reports.

How the Diversion Works

• Nurse with cabinet admin privileges removes fentanyl using "return to stock" or "waste disposal" functions — no patient attached, no MAR discrepancy, no pharmacist verification.
• Stock adjustment transactions — adjusting inventory counts to cover a missing controlled substance (e.g., recording a spill that never happened).
• Patient transfer/medication return transactions — when a patient is discharged, medications should be returned to pharmacy. But if the nurse has admin access, they can mark them as "returned to stock" in the cabinet without physically returning them.

Monitoring Strategy

-- ============================================================
-- ADMIN ACCESS CABINET TRANSACTIONS
-- ============================================================
-- Purpose: Every controlled substance removed from a
--   medication dispense cabinet via administrative access
--   (not associated with a patient encounter).
-- These transactions bypass standard dispense-vs-admin
--   matching and are invisible to most diversion surveillance.
-- ============================================================
SELECT
    staff_name,
        -- who performed the admin action
    access_mode,
        -- 'return_to_stock', 'inventory_adjustment',
        -- 'waste_disposal', 'override_all', 'manager_override'
    medication_name,
        -- what was removed
    quantity_removed,
        -- how many units/doses
    transaction_time,
        -- when — admin access at 3AM on a quiet unit
        -- is more suspicious than 2PM during a busy discharge
    cabinet_unit,
        -- which unit's cabinet
    -- Days since the last inventory count for this drug
    -- (a long gap + admin access = opportunity)
    DATEDIFF(day, last_inventory_date, GETDATE()) AS days_since_last_count
FROM cabinet_admin_access_log
WHERE transaction_date >= DATEADD(day, -30, GETDATE())
    AND medication_category = 'controlled'
ORDER BY transaction_time DESC;
    -- most recent first — review daily

Multidose vials are one of the easiest diversion targets because the remaining volume is almost never measured after each use. A single 10mL vial of fentanyl (50mcg/mL) contains 5mg — enough for 50 separate 100mcg doses. A nurse draws 2mL, charts 1mL for the patient, and keeps 1mL. The vial goes back in the drawer and the missing mL is invisible unless someone specifically tracks vial utilization.

The Diversion Mechanism

• Partial withdrawal without documentation — the nurse draws from the vial but doesn't chart the full volume removed. The remaining volume appears normal on visual inspection.
• Vial sharing between patients — a vial opened for Patient A is used for Patient B without a new dispense event. The second dose never hits the ADC audit trail.
• Extended vial open times — once opened, a multidose vial should be used within a defined window (typically 28 days for most, or per manufacturer). A vial that stays "open" long past its expected usage has been diverted.
• No per-use volume tracking — most ADC systems don't require staff to document the remaining volume when returning a multidose vial. Without that data point, you can't calculate utilization.

Monitoring Strategy

-- ============================================================
-- MULTIDOSE VIAL YIELD BY STAFF
-- ============================================================
-- Purpose: Find nurses who get fewer documented doses per
--   multidose vial than expected. If a 10mL fentanyl vial
--   should yield ~20 doses (at 0.5mL/dose) and one nurse
--   consistently gets 12-14 doses per vial, 6-8 doses per
--   vial are unaccounted for.
-- ============================================================
WITH vial_usage AS (
    SELECT
        staff_name,
        vial_id,
        medication_name,
        vial_size_mL,
        -- Expected doses = vial size / typical dose volume
        -- (adjust the divisor per your formulary)
        ROUND(vial_size_mL / 0.5, 0) AS expected_doses_per_vial,
        -- Actual documented administrations from this vial
        COUNT(DISTINCT administration_id) AS actual_doses_documented,
        -- How long the vial was in use (days)
        DATEDIFF(day, MIN(opened_at), MAX(closed_at)) AS vial_life_days
    FROM multidose_vial_log
    WHERE opened_at >= DATEADD(month, -3, GETDATE())
        AND medication_name IN ('fentanyl', 'morphine', 'hydromorphone',
                                'midazolam', 'ketamine')
    GROUP BY staff_name, vial_id, medication_name, vial_size_mL
)
SELECT
    staff_name,
    medication_name,
    COUNT(vial_id) AS vials_used,
    AVG(expected_doses_per_vial) AS avg_expected_doses,
    AVG(actual_doses_documented) AS avg_actual_doses,
    -- THE KEY METRIC: shortfall between expected and actual
    AVG(expected_doses_per_vial - actual_doses_documented)
        AS avg_shortfall_per_vial
FROM vial_usage
GROUP BY staff_name, medication_name
HAVING AVG(expected_doses_per_vial - actual_doses_documented) > 3
    -- more than 3 missing doses per vial on average
ORDER BY avg_shortfall_per_vial DESC;
    -- biggest diverter at the top

CRNAs and anesthesiologists operate with near-total autonomy over controlled substances. They draw, administer, and waste independently — often without direct oversight. A single provider can go through hundreds of doses per day, making it easy to hide diversion in the noise of high-volume practice.

The Diversion Mechanism

• Waste rate variance — a provider who wastes significantly more or less fentanyl, propofol, or succinylcholine than peers is the primary signal. Higher waste = diversion masked as waste. Lower waste = diversion masked as administration.
• Overdrawing from multidose vials — drawing more than needed for the case and diverting the excess before waste documentation.
• Case count anomalies — a provider who does more cases than peers (or picks up extra cases) has more opportunities to divert without raising suspicion.
• Propofol diversion is especially hard to detect because it's not a controlled substance in many formularies, it's used in large volumes, and waste is rarely measured precisely.

Monitoring Strategy

-- ============================================================
-- ANESTHESIA WASTE RATE ANALYSIS
-- ============================================================
-- Purpose: Identify anesthesia providers whose controlled
--   substance waste rates are statistical outliers compared
--   to peers, normalized by case volume.
-- High waste rate + low case volume = diversion risk.
-- ============================================================
WITH provider_draws AS (
    SELECT
        provider_name,
        medication_name,
        CASE WHEN medication_name IN ('fentanyl','hydromorphone',
             'morphine','midazolam') THEN 'opioid'
             WHEN medication_name IN ('propofol','ketamine',
             'etomidate') THEN 'anesthetic'
             ELSE 'other' END AS drug_category,
        SUM(draw_up_volume_mL) AS total_drawn,
            -- how much the provider drew up
        SUM(administered_volume_mL) AS total_administered,
            -- how much was given to the patient
        SUM(waste_volume_mL) AS total_wasted,
            -- how much was documented as waste
        COUNT(DISTINCT case_id) AS case_count
            -- total cases to normalize by
    FROM anesthesia_drug_log
    WHERE procedure_date >= DATEADD(month, -3, GETDATE())
    GROUP BY provider_name, medication_name
)
SELECT
    provider_name,
    medication_name,
    drug_category,
    total_drawn,
    total_administered,
    total_wasted,
    total_drawn - (total_administered + total_wasted) AS unaccounted_mL,
    -- KEY METRIC: waste as percentage of total drawn
    ROUND(100.0 * total_wasted / NULLIF(total_drawn, 0), 1) AS waste_pct,
    -- Cases per quarter
    case_count
FROM provider_draws
WHERE total_drawn > 50  -- meaningful sample size
ORDER BY waste_pct DESC;
    -- highest waste rate first

Used fentanyl patches still contain significant amounts of active drug — often 30-70% of the original dose. A 100mcg/hr patch worn for 72 hours still contains 1-3mg of fentanyl, enough for multiple recreational doses. Patches are small, easy to conceal, and hard to track once removed from a patient.

The Diversion Mechanism

• Used patch not returned — the nurse documents patch removal but the patch never makes it to the waste/disposal bin. It goes in a pocket instead.
• Falsified patch change documentation — charting a patch change that didn't happen. The "used" patch stays on the patient longer while the new patch is diverted.
• Partial patch diversion — cutting a used patch and taking a portion. The remaining piece is "returned" as the whole patch but weighs less.
• Witnessed destruction fraud — the nurse and witness both sign off on witnessed destruction, but the patch was never actually destroyed.

Monitoring Strategy

-- ============================================================
-- FENTANYL PATCH RETURN RECONCILIATION
-- ============================================================
-- Purpose: Every fentanyl patch dispensed should be
--   accounted for: administered, returned for destruction,
--   or documented as waste. Unreturned patches are the #1
--   diversion signal for transdermal fentanyl.
-- ============================================================
WITH patch_dispensed AS (
    SELECT
        encounter_id,
        COUNT(patch_id) AS patches_dispensed,
        SUM(patch_strength_mcg_hr) AS total_mcg_hr_dispensed
    FROM transdermal_dispense_log
    WHERE dispensed_at >= DATEADD(month, -3, GETDATE())
        AND medication_name = 'fentanyl'
    GROUP BY encounter_id
),
patch_returned AS (
    SELECT
        encounter_id,
        COUNT(patch_id) AS patches_returned,
        SUM(patch_strength_mcg_hr) AS total_mcg_hr_returned,
        -- Average weight of returned patches
        AVG(return_weight_g) AS avg_return_weight_g,
        -- Expected weight if properly worn
        AVG(expected_weight_g) AS avg_expected_weight_g
    FROM transdermal_return_log
    WHERE returned_at >= DATEADD(month, -3, GETDATE())
    GROUP BY encounter_id
)
SELECT
    d.encounter_id,
    d.patches_dispensed,
    COALESCE(r.patches_returned, 0) AS patches_returned,
    -- THE KEY METRIC: patches that left pharmacy but were
    -- never returned for destruction
    d.patches_dispensed - COALESCE(r.patches_returned, 0)
        AS missing_patches,
    -- Weight discrepancy: if returned weight is close to
    -- full weight, the patch may not have been worn
    CASE WHEN r.avg_return_weight_g IS NOT NULL
        AND r.avg_expected_weight_g IS NOT NULL
        AND r.avg_return_weight_g > r.avg_expected_weight_g * 0.8
        THEN 'FLAG - near full weight'
        ELSE 'OK'
    END AS weight_check
FROM patch_dispensed d
LEFT JOIN patch_returned r
    ON d.encounter_id = r.encounter_id
HAVING d.patches_dispensed - COALESCE(r.patches_returned, 0) > 0
    -- any missing patch needs investigation
ORDER BY missing_patches DESC;

After a patient expires, controlled substances in the room, on the body, or stored in the bedside cabinet are at high risk of diversion. The immediate post-mortem period is chaotic — family is present, staff are focused on the patient and family, and controlled substance accountability is often the last priority. This creates a window where medications can disappear without a trace.

The Diversion Mechanism

• Bedside controlled substances taken during the post-mortem period — medications in the patient's room (including POMs) that aren't immediately secured.
• ADC medications that were removed but not administered before death — fentanyl 50mcg drawn up for pain management 30 minutes before the patient expires. The medication was never given but was also never wasted or returned.
• Patient-controlled analgesia pumps not reconciled at time of death — the cassette may still contain significant volume that needs to be accounted for.
• Fentanyl patches on the deceased — patches are frequently missed during post-mortem care and discovered later by funeral home staff, or are removed and not returned for destruction.

Monitoring Strategy

-- ============================================================
-- POST-MORTEM CONTROLLED SUBSTANCE RECONCILIATION
-- ============================================================
-- Purpose: Find controlled substances dispensed to patients
--   in the 12 hours before death that were never
--   administered, wasted, or returned. Every such
--   transaction represents a potential diversion risk
--   during the post-mortem window.
-- ============================================================
WITH expired_patients AS (
    SELECT
        encounter_id,
        patient_name,
        unit,
        time_of_death
    from patient_census
    WHERE time_of_death >= DATEADD(month, -1, GETDATE())
        AND time_of_death IS NOT NULL
),
pre_death_dispenses AS (
    SELECT
        e.encounter_id,
        e.patient_name,
        e.unit,
        e.time_of_death,
        c.medication_name,
        c.dispensed_quantity,
        c.dispensed_at,
        c.administered_flag,
        c.wasted_flag,
        c.returned_to_pharmacy_flag
    FROM cabinet_transaction_log c
    JOIN expired_patients e
        ON c.encounter_id = e.encounter_id
    WHERE c.dispensed_at BETWEEN
            DATEADD(hour, -12, e.time_of_death)  -- 12h before death
            AND DATEADD(hour, 2, e.time_of_death)  -- 2h after death
        AND c.medication_category = 'controlled'
)
SELECT
    encounter_id,
    patient_name,
    unit,
    time_of_death,
    medication_name,
    dispensed_quantity,
    dispensed_at,
    -- Was the medication accounted for?
    CASE WHEN administered_flag = 0
            AND wasted_flag = 0
            AND returned_to_pharmacy_flag = 0
         THEN 'UNACCOUNTED - INVESTIGATE'
         WHEN administered_flag = 1 THEN 'Administered'
         WHEN wasted_flag = 1 THEN 'Wasted'
         WHEN returned_to_pharmacy_flag = 1 THEN 'Returned'
    END AS disposition
FROM pre_death_dispenses
WHERE administered_flag = 0
    AND wasted_flag = 0
    AND returned_to_pharmacy_flag = 0
    -- Only show items that are unaccounted for
ORDER BY time_of_death DESC;

Controlled substance waste must be witnessed at the time of administration — not 2 hours later, not at the end of shift. Every minute between administration and waste documentation is a minute where the drug is unaccounted for. Yet most diversion programs don't track this timing gap at all.

The Diversion Mechanism

• Documentation delay creates opportunity — the nurse removes 2mg of hydromorphone, charts 1mg as administered, and documents 1mg as waste 3 hours later. For those 3 hours, the drug was in the nurse's possession. The waste may or may not have actually occurred.
• End-of-shift waste batching — a nurse who documents all their waste for the shift in a 10-minute window at shift change is almost certainly not wasting in real time. This is a strong diversion signal.
• Waste witness collusion — if the same two people are always each other's waste witnesses, and they both have high waste documentation lag, they may be covering for each other.
• Zero lag + regular waste — paradoxically, a nurse who always documents waste immediately might be falsifying the timing. If administration was at 1400 and waste was documented at 1401, but the patient was in the OR at 1401, the data is fabricated.

Monitoring Strategy

-- ============================================================
-- WASTE DOCUMENTATION LAG BY STAFF
-- ============================================================
-- Purpose: Identify staff whose waste documentation is
--   consistently delayed past the 60-minute threshold.
--   Every minute of lag is a minute the drug was
--   unaccounted for and potentially diverted.
-- ============================================================
SELECT
    w.staff_name,
    w.unit_name,
    COUNT(w.waste_id) AS waste_events,
    -- Average time between administration and waste doc
    AVG(DATEDIFF(minute, w.administered_at, w.waste_documented_at))
        AS avg_lag_minutes,
    -- How often waste is documented more than 60 min late
    SUM(CASE WHEN DATEDIFF(minute,
        w.administered_at, w.waste_documented_at) > 60
        THEN 1 ELSE 0 END) AS late_waste_events,
    -- How often waste is documented in end-of-shift batches
    -- (3+ waste events within the same minute = batching)
    SUM(CASE WHEN w.is_batched_end_of_shift = 1
        THEN 1 ELSE 0 END) AS batched_waste_events,
    -- Percentage of events that are late
    ROUND(100.0 *
        SUM(CASE WHEN DATEDIFF(minute,
            w.administered_at, w.waste_documented_at) > 60
            THEN 1 ELSE 0 END)
        / NULLIF(COUNT(w.waste_id), 0), 1) AS late_pct
FROM controlled_waste_log w
WHERE w.waste_documented_at >= DATEADD(month, -1, GETDATE())
GROUP BY w.staff_name, w.unit_name
HAVING AVG(DATEDIFF(minute,
    w.administered_at, w.waste_documented_at)) > 30
    -- average lag > 30 minutes triggers review
ORDER BY avg_lag_minutes DESC;
    -- worst offenders first