Report live hospital occupancy by ward from EMAP-star (before data migrated to EMAP-ops aka OMOP) #emap #uclh

live_hospital_occupancy_by_ward.sql

-- Current occupancy of the Hospital
SELECT ward,
  count(ward) as occupancy
  FROM (
SELECT DISTINCT ON (pp.value_as_string)
  m.mrn AS Mrn
  , pa.value_as_datetime AS Allocation_Time
  , pp.value_as_string AS Bed_Name
  , split_part(split_part(pp.value_as_string, E'^', 2), ' ', 1) AS ward
FROM live.patient_property pp
LEFT JOIN live.patient_fact pf on pf.fact_id = pp.parent_fact
  JOIN live.patient_property pa ON pp.parent_fact = pa.parent_fact
  JOIN live.encounter e on pf.encounter = e.encounter
  JOIN live.mrn_encounter me ON e.encounter_id = me.encounter
  JOIN live.mrn m ON me.mrn = m.mrn_id
WHERE pp.attribute = 9 -- Location
  -- AND pp.value_as_string like'T03^T03 %'
  AND pp.valid_until IS NULL
  AND pp.stored_until IS NULL
  AND pa.attribute = 7 -- Arrival time
  AND pa.valid_until IS NULL
  AND pa.stored_until IS NULL
  AND pf.valid_until IS NULL
  AND pf.stored_until IS NULL
  AND me.valid_until IS NULL
  AND me.stored_until IS NULL
  AND pp.parent_fact NOT IN (
    SELECT parent_fact 
      FROM live.patient_property 
      WHERE valid_until IS NULL
      AND stored_until IS NULL
      AND attribute = 8 -- Discharge time
      AND value_as_datetime IS NOT NULL
  )
ORDER BY pp.value_as_string
  , pa.value_as_datetime
  ) o
  GROUP BY o.ward
  ORDER BY o.ward