tpp schema

Unique address identifier.

Date patient moved to address.

Date patient moved out of address.

Type of address:

• 0 - Permanent
• 1 - Temporary

• 3 - Correspondence only

• Possible values: 0, 1, 3
  

Rural urban classification:

• 1 - Urban major conurbation
• 2 - Urban minor conurbation
• 3 - Urban city and town
• 4 - Urban city and town in a sparse setting
• 5 - Rural town and fringe
• 6 - Rural town and fringe in a sparse setting
• 7 - Rural village and dispersed

• 8 - Rural village and dispersed in a sparse setting

• Always >= 1 and <= 8
  

Index of Multiple Deprivation (IMD)
rank of each lower layer super output area (LSOA), rounded to the nearest 100, where
lower values represent more deprived areas. E.g. 1 is the most deprived LSOA in the country
and 32,844 is the least deprived (though in this field these are rounded to 0 and 32,800
respectively)

• Always >= 0, <= 32800, and a multiple of 100
  

Index of Multiple Deprivation (IMD) LSOA rank mapped to quintiles. NB this does not
return an integer 1-5, instead it is a string to make clear that 1 is the most, and 5 is the
least deprived. Possible values are:

• 1 (most deprived)
• 2
• 3
• 4
• 5 (least deprived)
• unknown

The number of lower layer super output areas (LSOAs) in 2011 was 32,844. As this is not divisible
by 5 the number of LSOAs in each quintile is not the same. We have used the same boundaries as
provided in the data available to download here with the first quintile containing
6,568 LSOAs, and the other 4 quintiles containing 6,569 LSOAs.

imd = addresses.imd_rounded
return case(
    # Although the lowest IMD rank is 1, we need to check >= 0 because
    # we're using the imd_rounded field rather than the actual imd
    when((imd >= 0) & (imd <= int(32844 * 1 / 5))).then("1 (most deprived)"),
    when(imd <= int(32844 * 2 / 5)).then("2"),
    when(imd <= int(32844 * 3 / 5)).then("3"),
    when(imd <= int(32844 * 4 / 5)).then("4"),
    when(imd <= int(32844 * 5 / 5)).then("5 (least deprived)"),
    otherwise="unknown",
)

Index of Multiple Deprivation (IMD) LSOA rank mapped to deciles. NB this does not
return an integer 1-10, instead it is a string to make clear that 1 is the most, and 10 is the
least deprived. Possible values are:

• 1 (most deprived)
• 2
• ...
• 9
• 10 (least deprived)
• unknown

The number of lower layer super output areas (LSOAs) in 2011 was 32,844. As this is not divisible
by 10 the number of LSOAs in each decile is not the same. We have used the same boundaries as
provided in the data available to download here with deciles 1, 2, 4, 6, 7 and 9
containing 3,284 LSOAs, and deciles 3, 5, 8 and 10 containing 3,285

imd = addresses.imd_rounded
return case(
    # Although the lowest IMD rank is 1, we need to check >= 0 because
    # we're using the imd_rounded field rather than the actual imd
    when((imd >= 0) & (imd <= int(32844 * 1 / 10))).then("1 (most deprived)"),
    when(imd <= int(32844 * 2 / 10)).then("2"),
    when(imd <= int(32844 * 3 / 10)).then("3"),
    when(imd <= int(32844 * 4 / 10)).then("4"),
    when(imd <= int(32844 * 5 / 10)).then("5"),
    when(imd <= int(32844 * 6 / 10)).then("6"),
    when(imd <= int(32844 * 7 / 10)).then("7"),
    when(imd <= int(32844 * 8 / 10)).then("8"),
    when(imd <= int(32844 * 9 / 10)).then("9"),
    when(imd <= int(32844 * 10 / 10)).then("10 (least deprived)"),
    otherwise="unknown",
)

Middle Layer Super Output Areas (MSOA) code.

• Matches regular expression: E020[0-9]{5}
  

Indicating whether a valid postcode is recorded for the patient.

Indicating whether the patient's address matched with a care home, using TPP's algorithm.

Indicating whether the patient's address matched with a care home that required nursing.

Indicating whether the patient's address matched with a care home that did not require nursing.

Return each patient's registered address as it was on the supplied date.

Where there are multiple registered addresses we prefer any which have a known
postcode (though we never have access to this postcode) as this is used by TPP
to cross-reference other data associated with the address, such as the MSOA or
index of multiple deprevation.

Where there are multiple of these we prefer the most recently registered address
and then, if there are multiple of these, the one with the longest duration. If
there's stil an exact tie we choose arbitrarily based on the address ID.

spanning_addrs = addresses.where(addresses.start_date <= date).except_where(
    addresses.end_date < date
)
ordered_addrs = spanning_addrs.sort_by(
    case(when(addresses.has_postcode).then(1), otherwise=0),
    addresses.start_date,
    addresses.end_date,
    addresses.address_id,
)
return ordered_addrs.last_for_patient()

</details>

Unique identifier for the spell used across the APCS tables.

• Never NULL
  

The admission date of the hospital provider spell.

The date of discharge from a hospital provider spell.

The destination of a patient on completion of a hospital provider spell, or that the patient died or was a stillbirth. The code is a 2 character string (e.g. 19 = usual place of residence, 54 = NHS run care home). Refer to this documentation for a full list of codes.

The method of discharge from a hospital provider spell. Currently the code is a 1 character string (e.g. 1 = discharged on clinical advice, 4 = died). Refer to this documentation for a full list of codes.

The core Healthcare Resource Group (HRG) code for the spell according to the derivations made by NHS Digital prior to import to the National Commissioning Data Repository (NCDR). HRGs are used to assign baseline tariff costs.

Code identifying admission method. Refer to APCS data source documentation for details of codes.

Code indicating primary diagnosis. This is not necessarily the primary reason for admission, and could represent an escalation/complication of initial reason for admission.

Code indicating secondary diagnosis. This is a single code giving the first listed secondary diagnosis, but there may other secondary diagnoses listed in the all_diagnoses field below.

List of all diagnoses as ICD-10 codes.

Note that the codes are not quite in the standard ICD-10 format in that they
omit the dot character e.g. instead of I80.1 it will be written I801.

The codes are arranged in clusters separated by commas, with each cluster
separated by two pipe characters (||). These separators may or may not be
surrounded by spaces. For example:

||E119 ,J849 ,K869 ,M069 ,Z824 ,Z867 ||I801 ,I802 ,N179 ,N183

A hospital "spell" is made up of 1 or more "episodes". The || is the separator
between episodes. I.e. the example above is a spell of two episodes with 6
diagnosis codes recorded in the first episode, and 4 recorded in the second.

This field can be queried using the
contains method.
This uses simple substring matching to find a code anywhere inside the
field. For example, to match the code N17.1 (Acute renal failure with
acute cortical necrosis) you could use:

apcs.where(apcs.all_diagnoses.contains("N171"))

You can take advantage of the hierarchical structure of ICD-10 by searching
the just the prefix of a code. For example to match all N17 (Acute renal
failure) codes you could use:

apcs.where(apcs.all_diagnoses.contains("N17"))

Finally there is also
contains_any_of.
So if you were looking for any of a list of ICD10 codes called icd10_diagnosis_codes you
could do:

apcs.where(apcs.all_diagnoses.contains_any_of(icd10_diagnosis_list))

List of all procedures as OPCS-4 codes.

Note that the codes are not quite in the standard OPCS-4 format in that they
omit the dot character e.g. instead of W23.2 it will be written W232.

The codes are arranged in clusters separated by commas, with each cluster
separated by two pipe characters (||). These separators may or may not be
surrounded by spaces. For example:

||E851,T124,X403||Y532,Z921

A hospital "spell" is made up of 1 or more "episodes". The || is the separator
between episodes. I.e. the example above is a spell of two episodes with 3
procedure codes recorded in the first episode, and 2 recorded in the second.

This field can be queried using the
contains method.
This uses simple substring matching to find a code anywhere inside the
field. For example, to match the code W23.2 (Secondary open reduction of
fracture of bone and extramedullary fixation HFQ) you could use:

apcs.where(apcs.all_procedures.contains("W232"))

You can take advantage of the hierarchical structure of OPCS-4 by searching
the just the prefix of a code. For example to match all X30 (Injection of therapeutic substance)
codes you could use:

apcs.where(apcs.all_procedures.contains("X30"))

Finally there is also
contains_any_of.
So if you were looking for any of a list of OPCS-4 codes called opcs4_procedure_codes you
could do:

apcs.where(apcs.all_procedures.contains_any_of(opcs4_procedure_list))

Number of days spent in critical care. This is counted in number of days (or part-days) not the number of nights as per normal "length of stay" calculations. Note the definition of critical care may vary between trusts.

Refer to APCS data source documentation for details.

Unique identifier for the spell used across the APCS tables.

• Never NULL
  

The grand total payment for the activity (Net_SLA_Payment + Tariff_MFF_Payment) where SLA = service level agreement, i.e. all contractual payments which is national tariff for the type of activity plus any additional payments minus any applicable deductions. MFF = Market Forces Factor, a geography-based cost adjustment).

The base national tariff.

The total payment according to the national tariff.

The admission date of the hospital provider spell.

The date of discharge from a hospital provider spell.

The date the appointment was booked

The date the appointment was due to start

The date the patient was seen

The status of the appointment

• Possible values: Booked, Arrived, Did Not Attend, In Progress, Finished, Requested, Blocked, Visit, Waiting, Cancelled by Patient, Cancelled by Unit, Cancelled by Other Service, No Access Visit, Cancelled Due To Death, Patient Walked Out
  

ID of the consultation associated with this event

The lower bound of the reference range associated with an event's
numeric_value

The upper bound of the reference range associated with an event's
numeric_value

If an event's numeric_value is returned with a comparator, e.g. as '<9.5',
then this column contains that comparator

• Possible values: ~, =, >=, >, <, <=
  

ID of the consultation associated with this event

Treatment setting/indication.

• Possible values: non_hospitalised, hospitalised_with, hospital_onset
  

Status of form/application.

• Possible values: Approved, Treatment Complete, Treatment Not Started, Treatment Stopped
  

Intervention or therapeutic name. Expected to be one of:

• Baricitinib
• Casirivimab and imdevimab
• Molnupiravir
• Paxlovid
• Remdesivir
• sarilumab (sic)
• Sotrovimab
• Tocilizumab

Date form submitted.

NHS England region in which the CMDU submitting the form is located.

High-risk group to which the patient was considered to belong. Derived from
tick-boxes. Multiple groups can be selected and will be comma separated,
e.g. liver disease,rare neurological conditions.

This series only contains data for events where the intervention was one of
Sotroviman, Molnupiravir, or Casirivimab & imdevimab.

The available groups as at the time of writing are listed below. However
note that the precise wording used has changed over time and so filtering by
a specific disease name may not be reliable.

• Downs syndrome
• HIV or AIDS
• IMID
• haematologic malignancy
• haematological diseases
• immune deficiencies
• liver disease
• primary immune deficiencies
• rare neurological conditions
• rare neurological diseases
• renal disease
• sickle cell disease
• solid cancer
• solid organ recipients
• stem cell transplant recipients

Entered by the clinician and can represent either a future planned start
date or a past date at the time of form submission.

Date of calculation for the decision support algorithm.

The value computed by the decision support algorithm

The description of the decision support algorithm.

The version of the decision support algorithm.

Returns every calculated electronic frailty index v1 (EFI) for each patient.

return decision_support_values.where(
    decision_support_values.algorithm_description == "UK Electronic Frailty Index (eFI)"
).where(decision_support_values.algorithm_version == "1.0")

</details>

Unique identifier for the attendance used across the EC tables.

• Never NULL
  

The date the patient self presented at the accident & emergency department, or arrived in an ambulance at the accident & emergency department.

The core Healthcare Resource Group (HRG) code derived by sus+, used for tariff application.

• Matches regular expression: [a-zA-Z]{2}[0-9]{2}[a-zA-Z]
  

Unique identifier for the attendance used across the EC tables.

• Never NULL
  

The grand total payment for the activity (Net_SLA_Payment + Tariff_MFF_Payment) where SLA = service level agreement, i.e. all contractual payments which is national tariff for the type of activity plus any additional payments minus any applicable deductions. MFF = Market Forces Factor, a geography-based cost adjustment).

The total payment according to the national tariff.

The date the patient self presented at the accident & emergency department, or arrived in an ambulance at the accident & emergency department.

The date a decision to admit was made (if applicable).

The date the patient was injured (if applicable).

Unique identifier for the attendance used across the EC tables.

• Never NULL
  

The date the patient self presented at the accident & emergency department, or arrived in an ambulance at the accident & emergency department.

The SNOMED CT concept ID which is used to identify the intended destination of the patient following discharge from the emergency care department.

The SNOMED CT concept ID which is used to identify the patient diagnosis. Note that only a limited subset of SNOMED CT codes are used; see the NHS Data Model and Dictionary entry for emergency care diagnosis.

The SNOMED CT concept ID which is used to identify the patient diagnosis. Note that only a limited subset of SNOMED CT codes are used; see the NHS Data Model and Dictionary entry for emergency care diagnosis.

The SNOMED CT concept ID which is used to identify the patient diagnosis. Note that only a limited subset of SNOMED CT codes are used; see the NHS Data Model and Dictionary entry for emergency care diagnosis.

The SNOMED CT concept ID which is used to identify the patient diagnosis. Note that only a limited subset of SNOMED CT codes are used; see the NHS Data Model and Dictionary entry for emergency care diagnosis.

The SNOMED CT concept ID which is used to identify the patient diagnosis. Note that only a limited subset of SNOMED CT codes are used; see the NHS Data Model and Dictionary entry for emergency care diagnosis.

The SNOMED CT concept ID which is used to identify the patient diagnosis. Note that only a limited subset of SNOMED CT codes are used; see the NHS Data Model and Dictionary entry for emergency care diagnosis.

The SNOMED CT concept ID which is used to identify the patient diagnosis. Note that only a limited subset of SNOMED CT codes are used; see the NHS Data Model and Dictionary entry for emergency care diagnosis.

The SNOMED CT concept ID which is used to identify the patient diagnosis. Note that only a limited subset of SNOMED CT codes are used; see the NHS Data Model and Dictionary entry for emergency care diagnosis.

The SNOMED CT concept ID which is used to identify the patient diagnosis. Note that only a limited subset of SNOMED CT codes are used; see the NHS Data Model and Dictionary entry for emergency care diagnosis.

The SNOMED CT concept ID which is used to identify the patient diagnosis. Note that only a limited subset of SNOMED CT codes are used; see the NHS Data Model and Dictionary entry for emergency care diagnosis.

The SNOMED CT concept ID which is used to identify the patient diagnosis. Note that only a limited subset of SNOMED CT codes are used; see the NHS Data Model and Dictionary entry for emergency care diagnosis.

The SNOMED CT concept ID which is used to identify the patient diagnosis. Note that only a limited subset of SNOMED CT codes are used; see the NHS Data Model and Dictionary entry for emergency care diagnosis.

The SNOMED CT concept ID which is used to identify the patient diagnosis. Note that only a limited subset of SNOMED CT codes are used; see the NHS Data Model and Dictionary entry for emergency care diagnosis.

The SNOMED CT concept ID which is used to identify the patient diagnosis. Note that only a limited subset of SNOMED CT codes are used; see the NHS Data Model and Dictionary entry for emergency care diagnosis.

The SNOMED CT concept ID which is used to identify the patient diagnosis. Note that only a limited subset of SNOMED CT codes are used; see the NHS Data Model and Dictionary entry for emergency care diagnosis.

The SNOMED CT concept ID which is used to identify the patient diagnosis. Note that only a limited subset of SNOMED CT codes are used; see the NHS Data Model and Dictionary entry for emergency care diagnosis.

The SNOMED CT concept ID which is used to identify the patient diagnosis. Note that only a limited subset of SNOMED CT codes are used; see the NHS Data Model and Dictionary entry for emergency care diagnosis.

The SNOMED CT concept ID which is used to identify the patient diagnosis. Note that only a limited subset of SNOMED CT codes are used; see the NHS Data Model and Dictionary entry for emergency care diagnosis.

The SNOMED CT concept ID which is used to identify the patient diagnosis. Note that only a limited subset of SNOMED CT codes are used; see the NHS Data Model and Dictionary entry for emergency care diagnosis.

The SNOMED CT concept ID which is used to identify the patient diagnosis. Note that only a limited subset of SNOMED CT codes are used; see the NHS Data Model and Dictionary entry for emergency care diagnosis.

The SNOMED CT concept ID which is used to identify the patient diagnosis. Note that only a limited subset of SNOMED CT codes are used; see the NHS Data Model and Dictionary entry for emergency care diagnosis.

The SNOMED CT concept ID which is used to identify the patient diagnosis. Note that only a limited subset of SNOMED CT codes are used; see the NHS Data Model and Dictionary entry for emergency care diagnosis.

The SNOMED CT concept ID which is used to identify the patient diagnosis. Note that only a limited subset of SNOMED CT codes are used; see the NHS Data Model and Dictionary entry for emergency care diagnosis.

The SNOMED CT concept ID which is used to identify the patient diagnosis. Note that only a limited subset of SNOMED CT codes are used; see the NHS Data Model and Dictionary entry for emergency care diagnosis.

First character of recorded ethncity code (national code):
https://www.datadictionary.nhs.uk/data_elements/ethnic_category.html

• Possible values: A, B, C, D, E, F, G, H, J, K, L, M, N, P, R, S
  

ID of the consultation associated with this event

Patient's date of death.

Patient's underlying cause of death.

Medical condition mentioned on the death certificate.

Medical condition mentioned on the death certificate.

Medical condition mentioned on the death certificate.

Medical condition mentioned on the death certificate.

Medical condition mentioned on the death certificate.

Medical condition mentioned on the death certificate.

Medical condition mentioned on the death certificate.

Medical condition mentioned on the death certificate.

Medical condition mentioned on the death certificate.

Medical condition mentioned on the death certificate.

Medical condition mentioned on the death certificate.

Medical condition mentioned on the death certificate.

Medical condition mentioned on the death certificate.

Medical condition mentioned on the death certificate.

Medical condition mentioned on the death certificate.

Patient's place of death.

• Possible values: Care Home, Elsewhere, Home, Hospice, Hospital, Other communal establishment
  

Match codelist against the underlying_cause_of_death field and all 15
separate cause_of_death fields.

This method evaluates as True if any code in the codelist matches any of
these fields.

columns = [
    "underlying_cause_of_death",
    *[f"cause_of_death_{i:02d}" for i in range(1, 16)],
]
conditions = [getattr(ons_deaths, column).is_in(codelist) for column in columns]
return functools.reduce(operator.or_, conditions)

</details>

Unique identifier for the appointment used across the OPA tables.

• Never NULL
  

The date of an appointment.

Indicates whether or not an appointment for a care contact took place. If the appointment did not take place it also indicates whether or not advanced warning was given. Refer to the NHS Data Model and Dictionary entry for "attended or did not attend" for details on code meanings.

• Possible values: 5, 6, 7, 2, 3, 4, 0
  

Identifies the communication mechanism used to relay information between the care professional and the person who is the subject of the consultation, during a care activity. Refer to the NHS Data Model and Dictionary entry for "consultation mechanism" for details on code meanings. Note that the allowed codes as listed in TPP's data appear to be a subset of the codes listed in the NHS Data Model and Dictionary.

• Possible values: 01, 02, 03, 04, 05, 09, 10, 11, 98
  

An indication of whether a patient is making a first attendance or contact; or a follow-up attendance or contact and whether the consultation medium used national code was face to face communication or telephone or telemedicine web camera. Refer to the NHS Data Model and Dictionary entry for "first attendance" for details on code meanings. Note that the allowed codes as listed in TPP's data contain an additional 9 code over the NHS Data Model and Dictionary entry.

• Possible values: 1, 2, 3, 4, 5, 9
  

The Healthcare Resource Group (HRG) code assigned to the activity, used to assign baseline tariff costs.

• Matches regular expression: [a-zA-Z]{2}[0-9]{2}[a-zA-Z]
  

The treatment function under which the patient is treated. It may be the same as the main specialty code or a different treatment function which will be the care professional's treatment interest.

Unique identifier for the appointment used across the OPA tables.

• Never NULL
  

The base national tariff where the procedure tariff is applicable.

The grand total payment for the activity (Net_SLA_Payment + Tariff_MFF_Payment) where SLA = service level agreement, i.e. all contractual payments which is national tariff for the type of activity plus any additional payments minus any applicable deductions. MFF = Market Forces Factor, a geography-based cost adjustment).

The total payment according to the national tariff.

The date of an appointment.

The date the referral request was received by the health care provider.

Unique identifier for the appointment used across the OPA tables.

• Never NULL
  

The international classification of diseases (ICD) code used to identify the primary patient diagnosis. Note that this will typically not be completed.

The Read coded clinical terms code to identify the primary patient diagnosis. Note that this will typically not be completed.

The international classification of diseases (ICD) code used to identify the secondary patient diagnosis. Note that this will typically not be completed.

The Read coded clinical terms used to identify the secondary patient diagnosis. Note that this will typically not be completed.

The date of an appointment.

The date the referral request was received by the health care provider.

Unique identifier for the appointment used across the OPA tables.

• Never NULL
  

The OPCS classification of interventions and procedures code which is used to identify the primary patient procedure carried out.

The Read coded clinical terms code which is used to identify the primary patient procedure carried out.

TODO

The Read coded clinical terms for a procedure other than the primary procedure.

The date of an appointment.

The date the referral request was received by the health care provider.

The response to the question, as a CTV3 code. Alternatively, if the question does not admit a CTV3 code as the response, then the question, as a CTV3 code.

• Never NULL
  

The response to the question, as a SNOMED CT code. Alternatively, if the question does not admit a SNOMED CT code as the response, then the question, as a SNOMED CT code.

The date the survey was administered

• Never NULL
  

The response to the question, as a date, if the question admits a date as the response. Alternatively, the date the survey was administered.

• Never NULL
  

The ID of the survey

• Never NULL
  

The response to the question, as a number

The patient_id of the patient's mother

Patient's date of birth.

• Always the first day of a month
• Never NULL
  

Patient's sex.

• Possible values: female, male, intersex, unknown
• Never NULL
  

Patient's date of death.

Patient's age as an integer, in whole elapsed calendar years, as it would be on
the given date.

This method takes no account of whether the patient is alive on the given date.
In particular, it may return negative values if the given date is before the
patient's date of birth.

return (date - patients.date_of_birth).years

</details>

Whether a patient is alive on the given date, based on the date of death
recorded in their primary care record. NB this is only based on the primary
care record. Please see the section above about the accuracy of death data.

If the date provided is before a person was born, then this helper function will
actually return True, despite the person not being alive yet. For most research
this is likely the expected behaviour.

return patients.date_of_death.is_after(date) | patients.date_of_death.is_null()

</details>

Whether a patient has a date of death in their primary care record before the given date.

A person is classed as dead if the date provided is after their death date.

return patients.date_of_death.is_not_null() & patients.date_of_death.is_before(date)

</details>

Date patient joined practice.

• Never NULL
  

Date patient left practice.

Pseudonymised practice identifier.

• Never NULL
  

ONS code of practice's STP (Sustainability and Transformation Partnership).
STPs have been replaced by ICBs (Integrated Care Boards), and ICB codes will be available soon.

• Matches regular expression: E540000[0-9]{2}
  

Name of the NUTS level 1 region of England to which the practice belongs.
For more information see:
https://www.ons.gov.uk/methodology/geography/ukgeographies/eurostat

• Possible values: North East, North West, Yorkshire and The Humber, East Midlands, West Midlands, East, London, South East, South West
  

Date on which the practice started using the SystmOne EHR platform.

Most patient records will have been transferred from the previous EHR
platform but records which are specific to SystmOne will not exist before
this date. In particular, the appointments table should
only be considered accurate for a given practice after this date.

Return each patient's practice registration as it was on the supplied date.

Where a patient is registered with multiple practices we prefer the most recent
registration and then, if there are multiple of these, the one with the longest
duration. If there's still an exact tie we choose arbitrarily based on the
practice ID.

spanning_regs = practice_registrations.where(practice_registrations.start_date <= date).except_where(
    practice_registrations.end_date < date
)
ordered_regs = spanning_regs.sort_by(
    practice_registrations.start_date,
    practice_registrations.end_date,
    practice_registrations.practice_pseudo_id,
)
return ordered_regs.last_for_patient()

</details>

Returns whether a person was registered with a practice on the supplied date.

NB. The implementation currently uses spanning(). It would also have been
valid to implement as
practice_registrations.for_patient_on(date).exists_for_patient(), but for
internal reasons that is less efficient.

return practice_registrations.spanning(date, date).exists_for_patient()

</details>

Filter registrations to just those spanning the entire period between
start_date and end_date.

return practice_registrations.where(
    practice_registrations.start_date.is_on_or_before(start_date)
    & (practice_registrations.end_date.is_after(end_date) | practice_registrations.end_date.is_null())
)

</details>

Filter registrations to just those spanning the entire period between
start_date and end_date and where the practice has been using the SystmOne
EHR platform throughout that period (see
systmone_go_live_date).

return practice_registrations.spanning(start_date, end_date).where(
    practice_registrations.practice_systmone_go_live_date <= start_date
)

</details>

Date on which specimen was collected.

• Never NULL
  

Whether the specimin tested positive for SARS-CoV-2.

• Never NULL
  

Date on which the labaratory reported the result.

• Never NULL
  

Whether the patient reported symptoms of COVID-19 at the time the specimen
was collected. May be NULL if unknown.

Provides information on whether a PCR test result exhibited "S-Gene Target
Failure" which can be used as a proxy for the presence of certain Variants
of Concern.

Results are provided as number between 0 and 9. We know the meaning of
some of these numbers based on an email from PHE:

0: S gene detected

Detectable S gene (CH3>0)

Detectable y ORF1ab CT value (CH1) <=30 and >0

Detectable N gene CT value (CH2) <=30 and >0

1: Isolate with confirmed SGTF

Undetectable S gene; CT value (CH3) =0

Detectable ORF1ab gene; CT value (CH2) <=30 and >0

Detectable N gene; CT value (CH1) <=30 and >0

9: Cannot be classified

Null are where the target is not S Gene. I think LFTs are currently
also coming across as 9 so will need to review those to null as well as
clearly this is a PCR only variable.

However the values 2, 4 and 8 also occur in this column and we don't
currently have documentation on their meaning.

• Always >= 0 and <= 9
  

Where a specific SARS-CoV-2 variant was identified this column provides the details.

This appears to be effectively a free-text field with a large variety of
possible values. Some have an obvious meaning e.g. B.1.617.2,
VOC-21JAN-02, VUI-21FEB-04.

Others less so e.g. VOC-22JAN-O1_probable:V-21OCT-01_low-qc.

Where a specific SARS-CoV-2 variant was identified this provides the method
used to do so.

• Possible values: Private Lab Sequencing, Reflex Assay, Sanger Provisional Result
  

The cohort of patients.

Values are:

• '2019_prevalence' - a prevalence cohort of patients alive and on RRT in December 2019
• '2020_prevalence' - a prevalence cohort of patients alive and on RRT in December 2020
• '2021_prevalence' - a prevalence cohort of patients alive and on RRT in December 2021
• '2020_incidence' - an incidence cohort of patients who started RRT in 2020

• '2020_ckd' - a snapshot prevalence cohort of patient with Stage 4 or 5 CKD who were reported to the UKRR to be under renal care in December 2020.

• Possible values: 2019_prevalence, 2020_prevalence, 2021_prevalence, 2020_incidence, 2020_ckd
  

The code of the main renal centre a patient is registered with

The latest start date for renal replacement therapy

Most recent creatinine held by UKRR

Most recent eGFR held by UKRR

The treatment modality at rrt_start_date.

Values such as ICHD, HHD, HD, PD, Tx.

The treatment modality from the prevalence data

Vaccination identifier.

The date the vaccination was administered.

Vaccine's target disease.

Vaccine's product name.

The treatment function

• Matches regular expression: [a-zA-Z0-9]{3}
  

The priority type.

Note that a small number of rows contain values which are not in the list
below. These are converted to NULL in this representation of the data. If
you need to access the original values, please see the corresponding raw
table.

• Possible values: routine, urgent, two week wait
  

The date the referral was received, for the referral that started the original pathway

Clock stop for the completed pathway

Clock start for the completed pathway

The waiting list type on completion of the pathway.

Note that a small number of rows contain values which are not in the list
below. These are converted to NULL in this representation of the data. If
you need to access the original values, please see the corresponding raw
table.

• Possible values: ORTT, IRTT, PTLO, PTLI, RTTO, RTTI
  

The Sunday of the week that the pathway relates to

The treatment function

• Matches regular expression: [a-zA-Z0-9]{3}
  

Latest clock start for this pathway period

The priority type.

Note that a small number of rows contain values which are not in the list
below. These are converted to NULL in this representation of the data. If
you need to access the original values, please see the corresponding raw
table.

• Possible values: routine, urgent, two week wait
  

The date the referral was received, for the referral that started the original pathway

If the pathway is open, then NULL

Latest clock start for this pathway. If the pathway is not a current pathway, then NULL.

National referral source code for the referral that created the original pathway

• Matches regular expression: [a-zA-Z0-9]{2}
  

The waiting list type.

Note that a small number of rows contain values which are not in the list
below. These are converted to NULL in this representation of the data. If
you need to access the original values, please see the corresponding raw
table.

• Possible values: ORTT, IRTT, ONON, INON, PTLO, PTLI, RTTO, RTTI
  

The Sunday of the week that the pathway relates to