Data Structure

There are three different activity types, each of which has its own set of tables:

inpatient (apc)
outpatient (opa)
A&E (aae or ecds)

There several layers to the data, presented in order of processing:

hes - this is the raw HES data without manipulation or any curation
raw_data - this is the HES data with curation to remove unnecessary (particularly duplicated) columns and create more helpful derived variables.
aggregated_data - this converts the row-level data into counts; it exists only for A&E and outpatients.
default - the data which underpins the Demand model directly and is filtered to only acute providers.

The following are the different permutations of these tables that are available on UDAL:

default
    default_apc
    default_apc_mitigators
    default_ecds
    default_opa
raw_data
    raw_data_apc
    raw_data_apc_mitigators
    raw_data_ecds
    raw_data_opa
    raw_data_opa_mitigators
hes
    hes_apc
    hes_opa
    hes_aae
    hes_apc_diagnoses
    hes_apc_births
    hes_opa_diagnoses
    hes_aae_diagnoses
    hes_apc_procedures
    hes_opa_procedures
    hes_aae_investigations
    hes_aae_treatments

Each of these can accessed by appending the table name to the schema udal_lake_mart.newhospitalprogramme.

So, for example, the full path to default_apc would be udal_lake_mart.newhospitalprogramme.default_apc.

Note about mitigation

APC

The TPMAs are stored for admitted patient care in the separate tables default_apc_mitigators and raw_data_apc_mitigators.

These can be joined to the respective main tables on the following keys: epikey, provider and fyear. epikey is necessary and sufficient; provider and fyear will speed the join up but are not necessary.

OPA

For outpatient data, we have a separate table in the raw_data layer: raw_data_opa_mitigators. This is joined to opa similarly to apc, but instead of epikey we use attendkey.

In the default layer, we do this using pre-existing fields in the data itself.

• Outpatient Consultant to Consultant Referrals: use is_cons_cons_ref column
• Outpatient Followup Appointment Reduction: NOT FIRST, NO PROCEDURES - use NOT has_procedures column and NOT is_first column
• Outpatient GP Referred First Attendance Reduction:IS FIRST AND IS GP REFERRED. use is_gp_ref column and is_first column
• Outpatient Convert to Tele-Attendance - anything that does NOT have procedures. use NOT has_procedures

ECDS

The ECDS data, rather than joining to a separate table, is based simply on four existing boolean fields which map to the four areas of mitigation:

is_frequent_attender
is_left_before_treatment
is_low_cost_referred_or_discharged
is_discharged_no_treatment

If any of these is true, then the attendance is in scope of that TPMA.