By following this guide, you will learn where to locate and access the Admission & Discharge Locations dataset within PCC.

In PCC replicated database, hospitals can be found in view_ods_external_facility.  External facilities represent many different location types, funeral homes, hospitals, other post acute operations and home.  

You might think searching for the primary key (ExternalLocationID) in the schema will help you find out how to join data.  It does not.

SELECT * FROM information_schema.columns WHERE column_name like '%ExternalLocationID%'

The query above returns one result and that is for itself!  Ouch.

In my opinion, the above makes figuring this out very challenging.  Let’s talk about how to join this data on the census records (view_ods_patient_census).  What we find here is the field AdmitDischargeLocationID.  Believe it or not, this field can be joined to view_ods_external_facility.  

It is a bit confusing since this field is used for two separate concepts (Admissions & Discharges).  How they handle this is by turning the ID’s either:

• + positive for admissions
• – negative for discharges

Knowing this, we can join by using the absolute function on  AdmitDischargeLocationID field.

Admission & Discharge Query Example:

View_ods_patient_census JOIN

View_ods_external_facility ON view_ods_external_facility.ExternalLocationID = ABS(view_ods_patient_census.AdmitDischargeLocationID)