Exemplar ventilator dashboard

[docsteveharris]

Initial issue just to capture the work that Teddy needs to do

• [x] quick tour of existing infrastructure and approach
  • [x] sql and heidi
  • [ ] docker
  • [ ] data anonymisation http://uclvlddpragae07:8091/lab? and review synth_test_data_vitals.ipynb and vitals.sql
  • [ ] deployment
• [x] sql reading (ask the group)
• [x] ask for exemplar sql scripts (1) to find ICU patients (2) to find observations

[docsteveharris]

Exemplar snippet for hunting observation data

SELECT 
 obty.display_name
,obs.visit_observation_type_id
,COUNT(*) n
FROM star.visit_observation obs
INNER JOIN star.visit_observation_type obty 
    ON obs.visit_observation_type_id = obty.visit_observation_type_id
WHERE obs.observation_datetime > NOW() - '12 HOURS'::INTERVAL
GROUP BY obs.visit_observation_type_id, obty.display_name

Additional example

SELECT
     lr.lab_result_id
    ,lr.result_last_modified_time
    ,lr.result_status
    ,lr.units
    ,lr.value_as_real

    ,ltd.test_lab_code
    ,ltd.lab_department
    ,ltd.lab_provider

FROM star.lab_result lr
INNER JOIN star.lab_test_definition ltd
    ON lr.lab_test_definition_id = ltd.lab_test_definition_id

WHERE 
--ltd.test_lab_code IN ('pO2', 'pCO2', 'pH')
ltd.lab_provider = 'ABL90_FLEX_PLUS'
AND
lr.result_last_modified_time > NOW() - '168 HOURS'::INTERVAL
--LIMIT 300
;

[teddyhla]

-- ICU only bed state - checked and correct.

WITH

beds AS (
    SELECT
         lo.location_id
        ,lo.location_string
        ,dept.name department
    FROM star.location lo
    LEFT JOIN star.department dept ON lo.department_id = dept.department_id
    WHERE
        (
        dept.name IN
    -- Built from Tower Report 14 Jun 2022
    -- NAME                        -- n emap locations
        (
        'UCH T03 INTENSIVE CARE',      -- 37
        'UCH T06 SOUTH PACU',          -- 22
        'GWB L01 CRITICAL CARE',       -- 12
        'WMS W01 CRITICAL CARE' ,       -- 11
        'UCH T01 ACUTE MEDICAL',
        'UCH T01 ENHANED CARE'
        )
        OR lo.location_string IN
        (
        'T06C^T06C BY08^BY08-36'
        )
    )

),

open_visits AS (
    SELECT
         lv.location_id
        ,lv.admission_datetime
        ,lv.hospital_visit_id
        ,row_number() over (partition BY lv.location_id ORDER BY lv.admission_datetime DESC) admission_tail
    FROM star.location_visit lv
    INNER JOIN beds on lv.location_id = beds.location_id
    WHERE lv.discharge_datetime IS NULL

),

open_visits_last AS (
    SELECT
    *
    FROM open_visits
    WHERE admission_tail = 1
),

open_visits_count AS (
    SELECT
         ov.location_id
        ,MAX(ov.admission_tail) open_visits_n
    FROM open_visits ov
    GROUP BY ov.location_id
),

closed_visits AS (
    SELECT
         lv.location_id
        ,lv.admission_datetime
        ,lv.discharge_datetime
        ,lv.hospital_visit_id
        ,row_number() over (partition BY lv.location_id ORDER BY lv.discharge_datetime DESC) discharge_tail
    FROM star.location_visit lv
    INNER JOIN beds on lv.location_id = beds.location_id
    WHERE lv.discharge_datetime IS NOT NULL

),

closed_visits_last AS (
    SELECT
    *
    FROM closed_visits
    WHERE discharge_tail = 1
)

SELECT
     beds.location_id
    ,beds.department
    ,beds.location_string
    ,ovl.admission_datetime ovl_admission
    ,ovl.hospital_visit_id ovl_hv_id
    ,ovc.open_visits_n
    ,cvl.admission_datetime cvl_admission
    ,cvl.discharge_datetime cvl_discharge
    ,cvl.hospital_visit_id cvl_hv_id

    ,CASE
        WHEN cvl.discharge_datetime > ovl.admission_datetime THEN 1 ELSE 0
     END ovl_ghost
    ,CASE
        WHEN cvl.discharge_datetime > ovl.admission_datetime OR ovl.admission_datetime IS NULL THEN 0 ELSE 1
     END occupied

    ,hv.patient_class
    ,live_mrn.mrn
    ,cd.lastname
    ,cd.firstname
    ,cd.date_of_birth

FROM beds
-- details of the last open visit to that bed
LEFT JOIN open_visits_last ovl ON ovl.location_id = beds.location_id
LEFT JOIN open_visits_count ovc ON ovc.location_id = beds.location_id
LEFT JOIN closed_visits_last cvl ON cvl.location_id = beds.location_id
LEFT JOIN star.hospital_visit hv ON hv.hospital_visit_id = ovl.hospital_visit_id
LEFT JOIN star.core_demographic cd ON hv.mrn_id = cd.mrn_id
-- get current hospital number
LEFT JOIN star.mrn original_mrn ON hv.mrn_id = original_mrn.mrn_id
-- get mrn to live mapping
LEFT JOIN star.mrn_to_live mtl ON hv.mrn_id = mtl.mrn_id
-- get live mrn
LEFT JOIN star.mrn live_mrn ON mtl.live_mrn_id = live_mrn.mrn_id

ORDER BY beds.location_string

;
;

[teddyhla]

Correction of above 04th July code with result_last_modified_datetime

SELECT
     lr.lab_result_id
    ,lr.result_last_modified_datetime
    ,lr.result_status
    ,lr.units
    ,lr.value_as_real

    ,ltd.test_lab_code
    ,ltd.lab_department
    ,ltd.lab_provider

FROM star.lab_result lr
INNER JOIN star.lab_test_definition ltd
    ON lr.lab_test_definition_id = ltd.lab_test_definition_id

WHERE 
--- abl 90 is a gas machine 
ltd.lab_provider = 'ABL90_FLEX_PLUS'
AND
lr.result_last_modified_datetime > NOW() - '168 HOURS'::INTERVAL
--LIMIT 300
;

[teddyhla]

Issues to get supervision with Steve

1. Verifications for EMAP / SQL 1a) hospital_visit_id is unique to each pt & each visit ? 1b) lab_order_id links lab_result_id 1c) clarify lab_battery_id multiple items for pH etc -- showcase my existing queries 1d) note times is now "datetime" 1e) pH/haemoglobin/PaCO2 in observation data - likely a mistake ? 1f)TcPo2/TCpCO2 ? 1g)

2. Using synthetic data

   • to extract this week's patient data using synthetic data jupyternotebook

3. Data carpentry ?

   • using pandas to clean and query individually or en-masse using JSON /nested list as per Daniel Henlon ? -- IMHO - carpentry in pandas is better as larger than DH's data

4. Moving forward with Dash python code

5. Do you want me to walk through the new data science fellows? 6

[teddyhla]

WITH

torders AS (
    SELECT * 
    FROM lab_order 
    WHERE hospital_visit_id = 620329650 AND lab_battery_id =141438748

),
— lab battery id result in pCO2 
— hospital visit id for patient DB

wresults AS (
    SELECT
     lr.lab_result_id
    ,lr.result_last_modified_datetime
    ,lr.result_status
    ,lr.units
    ,lr.value_as_real
    ,ltd.test_lab_code
    ,ltd.lab_department
    ,ltd.lab_provider
    ,lr.lab_order_id

    FROM star.lab_result lr
    INNER JOIN star.lab_test_definition ltd
    ON lr.lab_test_definition_id = ltd.lab_test_definition_id

    WHERE 
    --- abl 90 is a gas machine 
    ltd.lab_provider = 'ABL90_FLEX_PLUS'
    AND
    lr.result_last_modified_datetime > NOW() - '168 HOURS'::INTERVAL

)

-essentially need to intersect torders with wresults
SELECT 
    a.hospital_visit_id
    ,a.order_datetime
    ,a.lab_order_id
    ,a.source_system
    ,b. lab_result_id
    ,b.result_last_modified_datetime
    ,b.result_status
    ,b.units
    ,b.value_as_real
    ,b.test_lab_code
FROM torders a
LEFT JOIN wresults b ON a.lab_order_id = b.lab_order_id