Patient infection status

[docsteveharris]

From dashRep created by docsteveharris: docsteveharris#6

Looks like this could be generated from patient_condition and condition_type tables in star
There will be an many:one relationship so need to think about how to display
But could aggregate to label patient as needing SR?

[docsteveharris]

Example query below
Suggest build a webpage with a datatable (perhaps cached) that is populated from a generic query
will need some requirements that the query might return 'ward'
and then have a generic 'ward' filter option

Alternatively might want to delegate the 'census' step to the API and then the returned MRNs in a WHERE clause?

WITH icupts AS (
SELECT
 lv.admission_time
--,lo.location_string
,lv.hospital_visit_id 

--,bed.hl7string
--,bed.room_id
--,room.hl7string
,room.name
--,room.department_id
--,department.hl7string
,department.name
,department.speciality


FROM star.location_visit lv
LEFT JOIN star.location lo ON lv.location_id = lo.location_id
LEFT JOIN star.bed ON lo.bed_id = bed.bed_id
LEFT JOIN star.room ON lo.room_id = room.room_id
LEFT JOIN star.department ON room.department_id = department.department_id
WHERE SPLIT_PART(lo.location_string,'^',1) ~ '^(WSCC)'
	AND lv.discharge_time IS NULL
ORDER BY lv.admission_time DESC
--LIMIT 5
)
SELECT 
 icupts.*

,vo.mrn_id
,vo.patient_class
,vo.arrival_method

,co.condition_type_id
,ct.name
,co.onset_date
,co.status

FROM icupts
LEFT JOIN hospital_visit vo ON icupts.hospital_visit_id = vo.hospital_visit_id
INNER JOIN patient_condition co ON co.mrn_id = vo.mrn_id
LEFT JOIN condition_type ct ON co.condition_type_id = ct.condition_type_id
;