Open docsteveharris opened 2 years ago
first pass use these scripts to explore not 100% if this is doing what we expect
SELECT
pm.planned_movement_id
,pm.cancelled
,pm.event_datetime
,pm.event_type
,pm.hospital_visit_id
,loc.location_string
,dpt.name
,cd.firstname
,cd.lastname
,row_number() over (partition BY pm.hospital_visit_id ORDER BY pm.event_datetime DESC) pm_tail
FROM star.planned_movement pm
LEFT JOIN location loc ON pm.location_id = loc.location_id
LEFT JOIN department dpt ON loc.department_id = dpt.department_id
LEFT JOIN hospital_visit hv ON pm.hospital_visit_id = hv.hospital_visit_id
LEFT JOIN core_demographic cd ON hv.mrn_id = cd.mrn_id
--WHERE dpt.name IN ('UCH T03 INTENSIVE CARE')
WHERE pm.hospital_visit_id = 564447728
ORDER BY pm.planned_movement_id DESC
LIMIT 30
;
and
SELECT
lv.location_visit_id
,lv.admission_datetime
,lv.discharge_datetime
,loc.location_string
FROM star.location_visit lv
LEFT JOIN star.location loc ON lv.location_id = loc.location_id
WHERE lv.hospital_visit_id = 564447728
ORDER BY lv.admission_datetime DESC
--LIMIT 3
;
Zella mentioned to be careful with the joins A row represents a request and is only populated with a location later on So this can cause problems
Also need to chase the Sherlock (with Peter Vangelov)
Some manual inspection suggests that we're not seeing or handling 'transfer bed request - cancelled' messges
40466
and reason for acute bed 40465
(and 40468
)reason to reside etc. not available (email sent to Sarah/Stef to ask to add)
R UCLH ICU READY DISCHARGE 41222
(visit_observation_type_id 94961820
)
as of 3/8 this should be available https://github.com/inform-health-informatics/emap_documentation/blob/main/changelogs/2022-08-change_log.md 2022-09-05 this is still not working reliably we seem to be in the right ball park but there are frequently patients who I have flagged as having an assigned bed but who actually don't