EMIS: expose vaccine- and allergy-related information from separate tables

[HelenCEBM]

Codes related to vaccines (e.g. refusal) are stored in the immunisations table, not the observations table. Similarly allergies are stored in the allergy table (even when related to vaccines). Therefore they are not currently captured by the study definition in the way we extract this information in TPP (with_these_clnical_events)

[inglesp]

A solution would be to make with_these_clinical_events query the union of observations, allergies, and immunisations. I had assumed that this might perform terribly, but both the following take a similar amount of time, between 5s and 15s. (I have no idea whether there's any caching happening.)

select registration_id, hashed_organisation, snomed_concept_id
from (
    -- observation_all_orgs_v2
    select registration_id, hashed_organisation, snomed_concept_id, effective_date from observation_all_orgs_v2
    union all
    select registration_id, hashed_organisation, snomed_concept_id, effective_date from immunisation_all_orgs_v2
    union all
    select registration_id, hashed_organisation, snomed_concept_id, effective_date from allergy_all_orgs_v2
) where snomed_concept_id in (901701000000101)

select registration_id, hashed_organisation, snomed_concept_id
from observation_all_orgs_v2
where snomed_concept_id in (901701000000101)

@evansd should we do the same in TPP land?

[sebbacon]

I agree with this solution in principle. However I worry there is some ontological use of the different tables that we would be obscuring. I suppose in practice it boils down to if the same SNOMED code might appear in more than one underlying table, because if so, what then?

[HelenCEBM]

From the initial checks that @inglesp performed it seems that SNOMED codes were strictly in just one table (e.g. "allergy to COVID-19 vaccine" appears only in the Allergy table, never in the main table or the vaccine table). I can't imagine a problem with combining them, except theoretically that for generic codes e.g. "did not attend" you could assume that a vaccine appointment was not attended based on it appearing in the vaccine table, for example, and we would be losing this information. However, it may be that generic codes are not stored there anyway, and such information would not discernible this way in TPP, as far as I know.

We could check a bit more thoroughly e.g. find the top X codes in allergy and immunisations and see if they are present in the other tables (and if so, what are they)

[HelenCEBM]

To update/summarise:

• The EMIS allergy table (which we currently can't query at all via study definitions) contains, as well as food and drug allergies as expected, information on drug contraindications, intolerances and adverse reactions which is potentially required for a variety of studies around the usage of different drugs throughout the pandemic (e.g. this one @Jongmassey @brianmackenna).
• It's possible to query the immunisation table successfully using the existing study definition infrastructure by passing a list of SNOMED codes (e.g here). However, in order to use a single study definition across both backends, results from this field must be combined with the standard fields either within the study definition or afterwards.
• There is no overlap between codes found in the allergy and immunisation tables. There are a handful of codes which are found in both allergy and observation, or immunisation and observation (results here). I don't think there is anything here to stop us querying a union of these tables.
• Researchers can check their (SNOMED) codelists against the contents of the allergy and immunisation tables as per this document.

Some options:

• Make with_these_clinical_events query the union of observations and allergies, and immunisations - I think this would work well in EMIS where all use SNOMED codes, but if replicated in TPP this could be more complex given the vaccination table uses different codes and descriptions.
• Make with_these_clinical_events query the union of observations and allergies, keeping immunisations separate as we can already query it separately (downside - researchers still need to know which information is stored in the immunisations table that they might need to query, and they will usually have to combine information from the results of both fields where codelists span both and/or for consistency with TPP. However, this is likely restricted to studies that specifically involve vaccines so is unlikely to affect other studies).

[sebbacon]

if replicated in TPP this could be more complex given the vaccination table uses different codes and descriptions

We should check with TPP that they don't already have a SNOMED mapping they could make available. If they do, then the combined option seems the best one?

So I think this issue is blocked on that discussion.